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?

Institutionalize

Answer 1

This should work:

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

Sybille

Works in 2012 too. Gory details: msdn.microsoft.com/en-us/library/ms187742.aspx – Espinoza

Why not put the NOT NULL adjacent to the data type? It may be syntactically valid to put it after the constraint, but it seems confusing to put it there. – Creedon

@Creedon The "Generate scripts..." tools in SSMS and SSDT still default to putting the NOT NULL part at the very end of the line after the type, default constraint, and temporal-table GENERATED bits. It's hideous. – Jeconiah

With SQL Server 2019, NOT NULL DEFAULT '' was giving me an auto-generated name for the constraint. Vexingly, specifying DEFAULT '' NOT NULL (putting the default value first) created the constraint with the specified name. Both statements ran without errors, but only one used the name I gave in the statement. – Otway

Answer 2

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

Undone

I prefer this to the accepted answer as I can drop the default constraint without worrying about losing the NOT NULL constraint. – Clearstory

@Clearstory That makes no sense. The NOT NULL is not part of the constraint and the drop statement just references the constraint name – Coatbridge

@RogerWillcocks You are right, but it is clearer upon reading it that the NOT NULL is separate from the constraint. – Rizo

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);

Stately

The top 2 answers both name their constraints, so your statement "I would like to add some details, as the existing answers are rather thin: The most important hint is: You should never-ever create a constraint without an explicit name!" - seems somewhat redundant. – Undone

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

Okechuku

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];

Honky