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?

Yseulte

I think you need ADD CONSTRAINT, like in your first example. – Precedent

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

Thomasinathomasine

right on spot. if i understand it correctly microsoft needs to update their content: msdn.microsoft.com/en-us/library/ms187742%28v=sql.110%29.aspx – Yseulte

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 ; ` – Thomasinathomasine

wow you r right again :) microsoft needs to put those stars around "added" haha – Yseulte

Glad to see it helped you..:) – Thomasinathomasine

Answer 2

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.

Maeda

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. – Speedway

Answer 3

It should be -

ALTER TABLE MyTable
ALTER COLUMN CreateDate DATETIME NOT NULL;

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

Kirchhoff

Answer 4

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;

Clash

Is it recommended to execute these in reverse order, given that you may already have null values in CreateDate? I.e. you want to remove the null values by applying the default constraint, then make the column non-nullable. – Muumuu

Answer 5

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;

Shana