在Microsoft SQL Server中，我知道检查一个列是否存在默认约束并删除默认约束的查询是:
IF EXISTS(SELECT * FROM sysconstraints
WHERE id=OBJECT_ID('SomeTable')
AND COL_NAME(id,colid)='ColName'
AND OBJECTPROPERTY(constid, 'IsDefaultCnst')=1)
ALTER TABLE SomeTable DROP CONSTRAINT DF_SomeTable_ColName
但是由于以前版本的数据库的拼写错误，约束的名称可能是DF_SomeTable_ColName或DF_SmoeTable_ColName。
我如何删除默认约束没有任何SQL错误?默认的约束名称不会显示在INFORMATION_SCHEMA表中，这使得事情变得有点棘手。
因此，类似于'删除这个表/列中的默认约束'，或'删除DF_SmoeTable_ColName'，但如果它找不到它，不要给出任何错误。
当前回答
我有一些列创建了多个默认约束，所以我创建了以下存储过程:
CREATE PROCEDURE [dbo].[RemoveDefaultConstraints] @table_name nvarchar(256), @column_name nvarchar(256)
AS
BEGIN
DECLARE @ObjectName NVARCHAR(100)
START: --Start of loop
SELECT
@ObjectName = OBJECT_NAME([default_object_id])
FROM
SYS.COLUMNS
WHERE
[object_id] = OBJECT_ID(@table_name)
AND [name] = @column_name;
-- Don't drop the constraint unless it exists
IF @ObjectName IS NOT NULL
BEGIN
EXEC ('ALTER TABLE '+@table_name+' DROP CONSTRAINT ' + @ObjectName)
GOTO START; --Used to loop in case of multiple default constraints
END
END
GO
-- How to run the stored proc. This removes the default constraint(s) for the enabled column on the User table.
EXEC [dbo].[RemoveDefaultConstraints] N'[dbo].[User]', N'enabled'
GO
-- If you hate the proc, just get rid of it
DROP PROCEDURE [dbo].[RemoveDefaultConstraints]
GO
其他回答
下面的解决方案将从表中删除列的特定默认约束
Declare @Const NVARCHAR(256)
SET @Const = (
SELECT TOP 1 'ALTER TABLE' + YOUR TABLE NAME +' DROP CONSTRAINT '+name
FROM Sys.default_constraints A
JOIN sysconstraints B on A.parent_object_id = B.id
WHERE id = OBJECT_ID('YOUR TABLE NAME')
AND COL_NAME(id, colid)='COLUMN NAME'
AND OBJECTPROPERTY(constid,'IsDefaultCnst')=1
)
EXEC (@Const)
希望这能对有类似问题的人有所帮助。 在ObjectExplorer窗口中，选择您的数据库=> Tables，=> your table=> Constraints。如果客户是在创建列时定义的，则可以看到包含列名的缺省约束名。 然后使用:
ALTER TABLE yourTableName DROP CONSTRAINT DF__YourTa__NewCo__47127295;
(约束名只是一个例子)
我有一些列创建了多个默认约束，所以我创建了以下存储过程:
CREATE PROCEDURE [dbo].[RemoveDefaultConstraints] @table_name nvarchar(256), @column_name nvarchar(256)
AS
BEGIN
DECLARE @ObjectName NVARCHAR(100)
START: --Start of loop
SELECT
@ObjectName = OBJECT_NAME([default_object_id])
FROM
SYS.COLUMNS
WHERE
[object_id] = OBJECT_ID(@table_name)
AND [name] = @column_name;
-- Don't drop the constraint unless it exists
IF @ObjectName IS NOT NULL
BEGIN
EXEC ('ALTER TABLE '+@table_name+' DROP CONSTRAINT ' + @ObjectName)
GOTO START; --Used to loop in case of multiple default constraints
END
END
GO
-- How to run the stored proc. This removes the default constraint(s) for the enabled column on the User table.
EXEC [dbo].[RemoveDefaultConstraints] N'[dbo].[User]', N'enabled'
GO
-- If you hate the proc, just get rid of it
DROP PROCEDURE [dbo].[RemoveDefaultConstraints]
GO
删除多个列的约束:
declare @table_name nvarchar(256)
declare @Command nvarchar(max) = ''
set @table_name = N'ATableName'
select @Command = @Command + 'ALTER TABLE ' + @table_name + ' drop constraint ' + d.name + CHAR(10)+ CHAR(13)
from sys.tables t
join sys.default_constraints d on d.parent_object_id = t.object_id
join sys.columns c on c.object_id = t.object_id
and c.column_id = d.parent_column_id
where t.name = @table_name and c.name in ('column1','column2','column3')
--print @Command
execute (@Command)
这里有一个简单的解决方案，只需替换your_table和column_name。
DECLARE @var0 nvarchar(128)
SELECT @var0 = name
FROM sys.default_constraints
WHERE parent_object_id = object_id(N'${default_schema}.your_table')
AND col_name(parent_object_id, parent_column_id) = 'column_name';
IF @var0 IS NOT NULL
EXECUTE ('ALTER TABLE ${default_schema}.your_table DROP CONSTRAINT [' + @var0 + ']');
GO
