Can I create a named default constraint in an add column statement in SQL Server?

0 votes
asked Sep 22, 2010 by glenns

In SQL Server, I have a new column on a table:

ALTER TABLE t_tableName 
    ADD newColumn NOT NULL

This fails because I specify NOT NULL without specifying a default constraint. The table should not have a default constraint.

To get around this, I could create the table with the default constraint and then remove it.

However, there doesn't appear to be any way to specify that the default constraint should be named as part of this statement, so my only way to get rid of it is to have a stored procedure which looks it up in the sys.default_constraints table.

This is a bit messy/verbose for an operation which is likely to happen a lot. Does anyone have any better solutions for this?

4 Answers

0 votes
answered Sep 22, 2010 by glenns

I could create the column without the NOT NULL, run an update on the existing date, then alter the column to have the NOT NULL. I am concerned about the performance implications of this, for example if it were on a multi-million row table, so this seems less than ideal.

0 votes
answered Sep 22, 2010 by mitch-wheat
ALTER TABLE t_tableName 
    ADD newColumn int NOT NULL
        CONSTRAINT DF_defaultvalue DEFAULT (1)
0 votes
answered Sep 22, 2010 by joe-stefanelli

This should work:

ALTER TABLE t_tableName 
    ADD newColumn VARCHAR(50)
    CONSTRAINT YourContraintName DEFAULT '' NOT NULL
0 votes
answered Sep 15, 2017 by rahul-srivastava

This is the way to do it:

 ALTER TABLE { TABLE_NAME } 
       ADD { COLUMN_NAME } { DATA_TYPE } { NULL | NOT NULL } 
       CONSTRAINT { CONSTRAINT_NAME } DEFAULT { DEFAULT_VALUE }
Welcome to Q&A, where you can ask questions and receive answers from other members of the community.
Website Online Counter

...