Can I add a not null column without DEFAULT value

user_290766 0
Viewed 110675

Can I add a column which is I specify as NOT NULL,I don't want to specify the DEFAULT value but MS-SQL 2005 says:

ALTER TABLE only allows columns to be added that can contain nulls, or have a DEFAULT definition specified, or the column being added is an identity or timestamp column, or alternatively if none of the previous conditions are satisfied the table must be empty to allow addition of this column. Column 'test' cannot be added to non-empty table 'shiplist' because it does not satisfy these conditions.

If YES, please let me know the syntax, if No please specify the reason.

sql-server-2005 tsql sql-server sql
10 Answers

I used below approach it worked for me

Syntax:
ALTER TABLE <YourTable> ADD <NewColumn> <NewColumnType> NOT NULL DEFAULT <DefaultValue>

Example:
ALTER TABLE Tablename ADD ColumnName datetime NOT NULL DEFAULT GETDATE();
user_53528310

As an option you can initially create Null-able column, then update your table column with valid not null values and finally ALTER column to set NOT NULL constraint:

ALTER TABLE MY_TABLE ADD STAGE INT NULL
GO
UPDATE MY_TABLE SET <a valid not null values for your column>
GO
ALTER TABLE MY_TABLE ALTER COLUMN STAGE INT NOT NULL
GO
user_11640040
Related