222

How to change one attribute in a table using T-SQL to allow nulls (not null --> null)? Alter table maybe?

alejandro zuleta
  • 13,962
  • 3
  • 28
  • 48
CrazyMouse
  • 2,497
  • 3
  • 15
  • 7
  • 13
    SQL Server Management Studio creates a very complex script for such a simple task. That's why I got confused and checked stackoverflow. Maybe that is the point of the question... – Tillito Feb 16 '13 at 20:36

8 Answers8

385
-- replace NVARCHAR(42) with the actual type of your column
ALTER TABLE your_table
ALTER COLUMN your_column NVARCHAR(42) NULL
LukeH
  • 263,068
  • 57
  • 365
  • 409
  • 5
    Or just `ALTER TABLE your_table ALTER COLUMN your_column NVARCHAR(42)` as it will default to allowing nulls anyway if not specified explicitly otherwise. – Martin Smith Feb 13 '16 at 15:53
  • 3
    Note if needing to change multiple columns to allow null, then you will need to perform separate `ALTER TABLE .. ALTER COLUMN ..` commands – sonyisda1 Feb 17 '17 at 15:14
  • 2
    Note there are some cases where this might not work - see [this DBA Stack Exchange answer](https://dba.stackexchange.com/a/27293/158) if you're getting an `ALTER TABLE ALTER COLUMN failed because one or more objects access this column.` – Jarrod Dixon Sep 06 '17 at 19:09
57

Yes you can use ALTER TABLE as follows:

ALTER TABLE [table name] ALTER COLUMN [column name] [data type] NULL

Quoting from the ALTER TABLE documentation:

NULL can be specified in ALTER COLUMN to force a NOT NULL column to allow null values, except for columns in PRIMARY KEY constraints.

Daniel Vassallo
  • 337,827
  • 72
  • 505
  • 443
23

ALTER TABLE is right:

ALTER TABLE MyCustomers ALTER COLUMN CompanyName VARCHAR(20) NULL
Oded
  • 489,969
  • 99
  • 883
  • 1,009
  • 2
    Are you sure you need to re-define the constraints, and just the data type? [The MSDN article](http://msdn.microsoft.com/en-us/library/ms190273.aspx) doesn't mention that constraints would need to be redefined: "If NULL or NOT NULL is specified with ALTER COLUMN, new_data_type [(precision [, scale ])] must also be specified. If the data type, precision, and scale are not changed, specify the current column values." – Daniel Vassallo Oct 08 '10 at 11:34
  • @Daniel Vassallo - You are right. I was trying to be complete, but changing NULL/NOT NULL should be the only change. – Oded Oct 08 '10 at 11:56
5

For MySQL, MariaDB

ALTER TABLE [table name] MODIFY COLUMN [column name] [data type] NULL

Use MODIFY COLUMN instead of ALTER COLUMN.

Vijay Nandwana
  • 2,476
  • 4
  • 25
  • 42
4
ALTER TABLE public.contract_termination_requests
ALTER COLUMN management_company_id DROP NOT NULL;
slavoo
  • 5,798
  • 64
  • 37
  • 39
laxmi kalake
  • 159
  • 1
  • 5
  • 2
    @ÁronLőrincz the question is not about Postgres though. It is tagged SQL Server so this answer is incorrect. – Martin Smith Feb 13 '16 at 15:55
  • You're right, but it still helped me and I think it's an useful comment for people who find the question via Google. The question's title doesn't make it clear which database server it is about. – Aron Lorincz Feb 14 '16 at 16:29
1

I wrote this so I could edit all tables and columns to null at once:

select 
case
when sc.max_length = '-1' and st.name in ('char','decimal','nvarchar','varchar')
then
'alter table  [' + so.name + '] alter column [' + sc.name + '] ' + st.name + '(MAX) NULL'
when st.name in ('char','decimal','nvarchar','varchar')
then
'alter table  [' + so.name + '] alter column [' + sc.name + '] ' + st.name + '(' + cast(sc.max_length as varchar(4)) + ') NULL'
else
'alter table  [' + so.name + '] alter column [' + sc.name + '] ' + st.name + ' NULL'
end as query
from sys.columns sc
inner join sys.types st on st.system_type_id = sc.system_type_id
inner join sys.objects so on so.object_id = sc.object_id
where so.type = 'U'
and st.name <> 'timestamp'
order by st.name
Jeffrey Pallatt
  • 11
  • 1
1

This is the approach to do this: -

  1. Check whether the table or column exists or not.
  2. If yes, then alter the column. e.g:-
IF EXISTS (SELECT 1 FROM INFORMATION_SCHEMA.COLUMNS WHERE 
            TABLE_CATALOG = 'DBName' AND 
            TABLE_SCHEMA = 'SchemaName' AND
            TABLE_NAME = 'TableName' AND
            COLUMN_NAME = 'ColumnName')
BEGIN
    ALTER TABLE DBName.SchemaName.TableName ALTER COLUMN ColumnName [data type] NULL
END

If you don't have any schema then delete the schema line because you don't need to give the default schema.

Tilak Dewangan
  • 351
  • 1
  • 2
  • 19
1

So the simplest way is, 

alter table table_name change column_name column_name int(11) NULL;
Mohsin Younas
  • 324
  • 4
  • 12