用于添加默认约束的命令

使用直接的 T-SQL 添加默认约束似乎至少有两种方法。下面两个方法之间的唯一区别是第二个方法专门为约束创建一个名称，而第一个方法有一个由 SQLServer 生成的名称，对吗？

ALTER TABLE [Common].[PropertySetting] ADD DEFAULT ((1)) FOR [Active];
ALTER TABLE [Common].[PropertySetting] ADD CONSTRAINT [DF_PropertySetting_Active) DEFAULT ((1)) FOR [Active];
55639 次浏览
小开
最佳答案

Pretty much, yes for an ALTER TABLE

You can add a columnn with default in one step for CREATE or ALTER too.

ALTER TABLE dbo.TableName
ADD bar varchar(100) CONSTRAINT DF_Foo_Bar DEFAULT ('bicycle');


ALTER TABLE dbo.TableName
ADD bar varchar(100) DEFAULT ('bicycle');

As you noted, the system generates a name if one is not supplied. CONSTRAINT constraint_name is optional says MSDN. The same applies to any column or table CONSTRAINT

If the column was already created, and you only want to add a (named) DEFAULT constraint, then use:

ALTER TABLE dbo.TableName
ADD CONSTRAINT DF_Foo_Bar DEFAULT 'bicycle' FOR FieldName;

To have the system generate the DEFAULT constraint name (which will be of the form DF_{TableName}_{Column}_{8RandomChars}, e.g. DF_TableName_FieldName_12345678) then omit the CONSTRAINT <name> part, like so:

ALTER TABLE dbo.TableName
ADD DEFAULT 'bicycle' FOR FieldName;

提交答案