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

Question

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?

Answer 1

This should work:

ALTER TABLE t_tableName 
    ADD newColumn VARCHAR(50)
    CONSTRAINT YourContraintName DEFAULT '' NOT NULL

Answer 2

ALTER TABLE t_tableName 
    ADD newColumn int NOT NULL
        CONSTRAINT DF_defaultvalue DEFAULT (1)

Answer 3

I would like to add some details:

The most important hint is: You should never-ever create a constraint without an explicit name!

The biggest problem with unnamed constraints: When you execute this on various customer machines, you will get different/random names on each.
Any future upgrade script will be a real headache...

The general advise is:

• No constraint without a name!
• Use some naming convention e.g.
  • DF_TableName_ColumnName for a default constraint
  • CK_TableName_ColumnName for a check constraint
  • UQ_TableName_ColumnName for a unique constraint
  • PK_TableName for a primary key constraint

The general syntax is

TheColumn <DataType> Nullability CONSTRAINT ConstraintName <ConstraintType> <ConstraintDetails>

Try this here

You can add more constraints to each column and you can add additional constraints just as you add columns after a comma:

CREATE TABLE dbo.SomeOtherTable(TheIdThere INT NOT NULL CONSTRAINT PK_SomeOtherTable PRIMARY KEY)
GO
CREATE TABLE dbo.TestTable
(
 --define the primary key
 ID INT IDENTITY NOT NULL CONSTRAINT PK_TestTable PRIMARY KEY

 --let the string be unique (results in a unique index implicitly)
,SomeUniqueString VARCHAR(100) NOT NULL CONSTRAINT UQ_TestTable_SomeUniqueString UNIQUE

 --define two constraints, one for a default value and one for a value check
,SomeNumber INT NULL CONSTRAINT DF_TestTable_SomeNumber DEFAULT (0)
                     CONSTRAINT CK_TestTable_SomeNumber_gt100 CHECK(SomeNumber>100)

 --add a foreign key constraint
,SomeFK INT NOT NULL CONSTRAINT FK_TestTable_SomeFK FOREIGN KEY REFERENCES dbo.SomeOtherTable(TheIdThere)

 --add a constraint for two columns separately
,CONSTRAINT UQ_TestTable_StringAndNumber UNIQUE(SomeFK,SomeNumber)
);
GO

--insert some data

INSERT INTO dbo.SomeOtherTable VALUES(1);
INSERT INTO dbo.TestTable(SomeUniqueString,SomeNumber,SomeFK) VALUES('hello',111,1);
GO
INSERT INTO dbo.TestTable(SomeUniqueString,SomeNumber,SomeFK) 
VALUES('fails due to uniqueness of 111,1',111,1);

Answer 4

Try like below script-

ALTER TABLE DEMO_TABLE
ADD Column1 INT CONSTRAINT Def_Column1 DEFAULT(3) NOT NULL,
    Column2 VARCHAR(10) CONSTRAINT Def_Column2 DEFAULT('New') NOT NULL;
GO

Answer 5

I use the following when adding new columns and defaults to large tables. Execute each line separately:

ALTER TABLE dbo.[TableName] ADD [ColumnName] BIT NULL; /*null>no table rebuild*/

UPDATE rf SET rf.[ColumnName] = 0 FROM dbo.[TableName] rf WHERE rf.[ColumnName] IS NULL;

ALTER TABLE dbo.[TableName] ALTER COLUMN [ColumnName] BIT NOT NULL;

ALTER TABLE dbo.[TableName] ADD CONSTRAINT DF_[TableName]_[ColumnName] DEFAULT 0 FOR [ColumnName];