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?

I think you need `ADD CONSTRAINT`, like in your first example. — Andrew, Sep 12 '14 at 16:02

score 20 · Accepted Answer · answered Sep 12 '14 at 17:02

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

right on spot. if i understand it correctly microsoft needs to update their content: http://msdn.microsoft.com/en-us/library/ms187742%28v=sql.110%29.aspx — SZT, Sep 12 '14 at 17:14
no ..if you read the first line correctly then it `Specifies the properties of a column that is **added** to a table by using ALTER TABLE. So you can always write something as: `ALTER TABLE **ADD** COLUMN .. CONSTRAINT .. DEFAULT ; ` — Deepshikha, Sep 12 '14 at 17:27
wow you r right again :) microsoft needs to put those stars around "added" haha — SZT, Sep 16 '14 at 19:31

score 11 · Answer 2 · edited Feb 25 '21 at 00:53

There is no direct way to change default value of a column in SQL server, but the following parameterized script will do the work:

DECLARE @table NVARCHAR(100);
DECLARE @column NVARCHAR(100);
DECLARE @newDefault NVARCHAR(100);
SET @table = N'TableName';
SET @column = N'ColumnName';
SET @newDefault = N'0';

IF EXISTS (
    SELECT   name
    FROM     sys.default_constraints
    WHERE    parent_object_id = OBJECT_ID(@table)
    AND      parent_column_id = COLUMNPROPERTY(OBJECT_ID(@table), @column, 'ColumnId')
)
BEGIN
    DECLARE @constraintName AS NVARCHAR(200);
    DECLARE @constraintQuery AS NVARCHAR(2000);

    SELECT  @constraintName = name
    FROM    sys.default_constraints
    WHERE   parent_object_id = OBJECT_ID(@table)
    AND parent_column_id = COLUMNPROPERTY(OBJECT_ID(@table), @column, 'ColumnId');

    SET @constraintQuery = N'ALTER TABLE ' + @table + N' DROP CONSTRAINT '
        + @constraintName + N'; ALTER TABLE ' + @table + N' ADD CONSTRAINT '
        + @constraintName + N' DEFAULT ' + @newDefault + N' FOR ' + @column;

    EXECUTE sp_executesql @constraintQuery;
END;

Just fill the parameters and execute. The script removes existing constraint and creates a new one with designated default value.

This is the best answer for all the questions related to 'how do I change a column's default value'? It takes into account that there is an existing default, and that default may have a system generated name, and it can be scripted and does not rely on manual observation/manipulation. — MickeyfAgain_BeforeExitOfSO, Sep 11 '17 at 15:03

score 7 · Answer 3 · answered Sep 12 '14 at 16:13

7

It should be -

ALTER TABLE MyTable
ALTER COLUMN CreateDate DATETIME NOT NULL;

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

answered Sep 12 '14 at 16:13

Krishnraj Rana

6,516
2
29
36

score 6 · Answer 4 · answered Sep 12 '14 at 16:12

6

For altering an existing column you need to:

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

answered Sep 12 '14 at 16:12

Alex K.

171,639
30
264
288

score 1 · Answer 5 · answered Sep 12 '14 at 16:12

From MSDN ALTER TABLE examples:

D. Adding a DEFAULT constraint to an existing column

The following example creates a table with two columns and inserts a value into the first column, and the other column remains NULL. A DEFAULT constraint is then added to the second column. To verify that the default is applied, another value is inserted into the first column, and the table is queried.

CREATE TABLE dbo.doc_exz ( column_a INT, column_b INT) ;
GO
INSERT INTO dbo.doc_exz (column_a)VALUES ( 7 ) ;
GO
ALTER TABLE dbo.doc_exz
ADD CONSTRAINT col_b_def
DEFAULT 50 FOR column_b ;
GO
INSERT INTO dbo.doc_exz (column_a) VALUES ( 10 ) ;
GO
SELECT * FROM dbo.doc_exz ;
GO
DROP TABLE dbo.doc_exz ;
GO

So in short, you need the ADD CONSTRAINT

ALTER TABLE MyTable 
    ALTER COLUMN CreateDate DATETIME NOT NULL ;

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

Alter column default value

5 Answers5