How to change one attribute in a table using T-SQL to allow nulls (not null --> null)? Alter table maybe?
+1
A:
ALTER TABLE is right:
ALTER TABLE MyCustomers ALTER COLUMN CompanyName VARCHAR(20) NULL
Oded
2010-10-08 11:15:41
You need to specify the column's type too.
LukeH
2010-10-08 11:19:34
@LukeH - quite right. If you read the description _after_ the example, you will see I noted that. Answer updated for a more accurate example.
Oded
2010-10-08 11:28:22
@Oded: 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
2010-10-08 11:34:09
@Daniel Vassallo - You are right. I was trying to be complete, but changing NULL/NOT NULL should be the only change.
Oded
2010-10-08 11:56:17
+1
A:
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:
NULLcan be specified inALTER COLUMNto force aNOT NULLcolumn to allow null values, except for columns in PRIMARY KEY constraints.
Daniel Vassallo
2010-10-08 11:16:11
+2
A:
-- replace NVARCHAR(42) with the actual type of your column
ALTER TABLE your_table
ALTER COLUMN your_column NVARCHAR(42) NULL
LukeH
2010-10-08 11:16:32