我可以在SQL Server中的ADD COLUMN语句中创建命名的DEFAULT约束吗？

在SQL Server中，我在表上有一个新列：

ALTER TABLE t_tableName
ADD newColumn NOT NULL

这会失败，因为我在没有指定默认约束的情况下指定了NOT NULL.表不应具有默认约束。

为了解决这个问题，我可以创建带有默认约束的表，然后删除它。

但是，似乎没有任何方法可以指定默认约束应作为此语句的一部分进行命名，因此我删除它的唯一方法是使用一个存储过程，该存储过程在sys.default_约束表中查找它。

对于一个可能经常发生的操作来说，这有点混乱/冗长。有人对此有更好的解决方案吗？

106015 次浏览
小开
最佳答案

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

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
小开

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

提交答案