Sql – Alter column default value

default valuenotnullsqlsql server

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?

Best Answer

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!!!