Can I create a named default constraint in an add column statement in SQL Server?
This should work:
ALTER TABLE t_tableName ADD newColumn VARCHAR(50) CONSTRAINT YourContraintName DEFAULT '' NOT NULL
ALTER TABLE t_tableName ADD newColumn int NOT NULL CONSTRAINT DF_defaultvalue DEFAULT (1)
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 constraintCK_TableName_ColumnName
for a check constraintUQ_TableName_ColumnName
for a unique constraintPK_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)GOCREATE 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);GOINSERT INTO dbo.TestTable(SomeUniqueString,SomeNumber,SomeFK) VALUES('fails due to uniqueness of 111,1',111,1);