为什么 SQL Server 不断创建 DF 约束？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/7663390/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Why does SQL Server keep creating a DF constraint?
提问by WEFX
I'm trying to create upgrade and backout scripts in SQL. The upgrade script adds a column like so:
我正在尝试在 SQL 中创建升级和退出脚本。升级脚本添加了一列，如下所示：
IF NOT EXISTS (SELECT * FROM sys.columns WHERE Name = N'ColumnName'
AND object_id = OBJECT_ID(N'[dbo].[TableName]'))
ALTER TABLE TableName
ADD ColumnName bit NOT NULL DEFAULT(0)
The backout script removes the column like so:
退出脚本像这样删除列：
IF EXISTS (SELECT * FROM sys.columns WHERE Name = N'ColumnName'
AND object_id = OBJECT_ID(N'[dbo].[TableName]'))
ALTER TABLE TableName
DROP COLUMN ColumnName
However, the backout script throws this error:
但是，退出脚本会引发此错误：
Msg 5074, Level 16, State 1, Line 5
The object 'DF__TableName__ColumnName__1BF3D5BD' is dependent on column 'ColumnName'.
Msg 4922, Level 16, State 9, Line 5
ALTER TABLE DROP COLUMN ColumnName failed because one or more objects access this column.
I know how to drop the constraint, but the constraint's name changes everytime (the suffix changes). I either need SQL Server to stop creating this randomly-named constraint OR I need to be able to remove the constraint in my script using wild-card characters, since the name changes.
我知道如何删除约束，但约束的名称每次都会更改（后缀更改）。我要么需要 SQL Server 停止创建这个随机命名的约束，要么我需要能够使用通配符删除脚本中的约束，因为名称已更改。
回答by Martin Smith
This is the defaultconstraint that is added because of the DEFAULT(0)in your newly added column.
这是default由于DEFAULT(0)在新添加的列中添加的约束。
You can name this yourself so it has a known fixed name rather than relying on the auto name generation.
您可以自己命名它，使其具有已知的固定名称，而不是依赖于自动名称生成。
ALTER TABLE TableName
ADD ColumnName bit NOT NULL CONSTRAINT DF_Some_Fixed_Name DEFAULT(0)
Then to remove the column and constraint together
然后将列和约束一起删除
ALTER TABLE dbo.TableName
DROP CONSTRAINT DF_Some_Fixed_Name, COLUMN ColumnName
回答by Kenneth
Run this:
运行这个：
declare @name as nvarchar(255);
SELECT @name = name FROM dbo.sysobjects
WHERE name LIKE 'DF__XXX__YYY__%' and type = 'D'
IF @name IS NOT NULL BEGIN
EXEC('ALTER TABLE XXX DROP CONSTRAINT ' + @name);
END
回答by Mateusz Kraska
Run this if you want remove constraint:
如果要删除约束，请运行此命令：
DECLARE @tableName NVARCHAR(255) = '[INSERT]';
DECLARE @first5CharsFromColumnName NVARCHAR(255) = '[INSERT]';
DECLARE @name NVARCHAR(255);
SELECT @name = d.name FROM dbo.sysobjects d
INNER JOIN dbo.sysobjects t ON t.id = d.parent_obj
WHERE d.name LIKE '%'+@first5CharsFromColumnName+'%' AND d.type = 'D' AND t.name = @tableName
IF @name IS NOT NULL BEGIN
EXEC('ALTER TABLE '+@tableName+' DROP CONSTRAINT ' + @name);
END
