Alter column default value

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?

Comments 5

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

  • 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;
    
  • 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;
    
  • It should be –

    ALTER TABLE MyTable
    ALTER COLUMN CreateDate DATETIME NOT NULL;
    
    ALTER TABLE MyTable 
    ADD CONSTRAINT DF_Constraint DEFAULT GetDate() FOR CreateDate;
    
  • 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='TableName'
    SET @column='ColumnName'
    SET @newDefault='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 = 'ALTER TABLE '+@table+' DROP CONSTRAINT '+@constraintName +'; ALTER TABLE '+ @table 
            + ' ADD CONSTRAINT ' + @constraintName +' DEFAULT '+@newDefault+' 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.

发表评论

电子邮件地址不会被公开。 必填项已用*标注