Alter column default value

Question

I know you can change the default value of an existing column like this:

ALTER TABLE Employee ADD CONSTRAINT DF_SomeName DEFAULT N'SANDNES' FOR CityBorn; 

But according to this my query supposed to work:

ALTER TABLE MyTable ALTER COLUMN CreateDate DATETIME NOT NULL        CONSTRAINT DF_Constraint DEFAULT GetDate() 

So here I'm trying to make my column Not Null and also set the Default value. But getting Incoorect Syntax Error near CONSTRAINT. Am I missing sth?

Answer 1

I think issue here is with the confusion between Create Table and Alter Table commands. If we look at Create table then we can add a default value and default constraint at same time as:

<column_definition> ::=  column_name <data_type>     [ FILESTREAM ]     [ COLLATE collation_name ]      [ SPARSE ]     [ NULL | NOT NULL ]     [          [ CONSTRAINT constraint_name ] DEFAULT constant_expression ]        | [ IDENTITY [ ( seed,increment ) ] [ NOT FOR REPLICATION ]      ]     [ ROWGUIDCOL ]     [ <column_constraint> [ ...n ] ]      [ <column_index> ]  ex:  CREATE TABLE dbo.Employee  (      CreateDate datetime NOT NULL       CONSTRAINT DF_Constraint DEFAULT (getdate()) )  ON PRIMARY; 

you can check for complete definition here: http://msdn.microsoft.com/en-IN/library/ms174979.aspx

but if we look at the Alter Table definition then with ALTER TABLE ALTER COLUMN you cannot add CONSTRAINT the options available for ADD are:

 | ADD      {          <column_definition>       | <computed_column_definition>       | <table_constraint>        | <column_set_definition>      } [ ,...n ] 

Check here: http://msdn.microsoft.com/en-in/library/ms190273.aspx

So you will have to write two different statements one for Altering column as:

ALTER TABLE MyTable ALTER COLUMN CreateDate DATETIME NOT NULL; 

and another for altering table and add a default constraint

ALTER TABLE MyTable ADD CONSTRAINT DF_Constraint DEFAULT GetDate() FOR CreateDate;

Hope this helps!!!