我可以删除一个表，如果它存在使用以下代码，但不知道如何做同样的约束:
IF EXISTS(SELECT 1 FROM sys.objects WHERE OBJECT_ID = OBJECT_ID(N'TableName') AND type = (N'U')) DROP TABLE TableName
go
我还使用以下代码添加了约束:
ALTER TABLE [dbo].[TableName]
WITH CHECK ADD CONSTRAINT [FK_TableName_TableName2] FOREIGN KEY([FK_Name])
REFERENCES [dbo].[TableName2] ([ID])
go
当前回答
IF (OBJECT_ID('DF_Constraint') IS NOT NULL)
BEGIN
ALTER TABLE [dbo].[tableName]
DROP CONSTRAINT DF_Constraint
END
其他回答
在SQL Server 2016中，你可以使用DROP IF EXISTS:
CREATE TABLE t(id int primary key,
parentid int
constraint tpartnt foreign key references t(id))
GO
ALTER TABLE t
DROP CONSTRAINT IF EXISTS tpartnt
GO
DROP TABLE IF EXISTS t
见https://web.archive.org/web/20151105064708/http: / / blogs.msdn.com/b/sqlserverstorageengine/archive/2015/11/03/drop——如果存在-新- sql - server - 2016. - aspx
埃里克·艾萨克斯给出了更简单的答案。但是，它可以在任何表上找到约束。如果你想在一个特定的表上指定一个外键约束，使用这个:
IF EXISTS (SELECT *
FROM sys.foreign_keys
WHERE object_id = OBJECT_ID(N'FK_TableName_TableName2')
AND parent_object_id = OBJECT_ID(N'dbo.TableName')
)
ALTER TABLE [dbo].[TableName] DROP CONSTRAINT [FK_TableName_TableName2]
ALTER TABLE [dbo].[TableName]
DROP CONSTRAINT FK_TableName_TableName2
好吧，我知道我迟到了，但是下面是我认为最好的语法。如果需要，向OBJECT_ID子句添加模式名。
IF OBJECTPROPERTY(OBJECT_ID(N'My_FK_name'),'IsConstraint') =1
ALTER TABLE dbo.TableName DROP CONSTRAINT My_FK_name
我想这会对你有帮助……
DECLARE @ConstraintName nvarchar(200)
SELECT
@ConstraintName = KCU.CONSTRAINT_NAME
FROM INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS AS RC
INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE AS KCU
ON KCU.CONSTRAINT_CATALOG = RC.CONSTRAINT_CATALOG
AND KCU.CONSTRAINT_SCHEMA = RC.CONSTRAINT_SCHEMA
AND KCU.CONSTRAINT_NAME = RC.CONSTRAINT_NAME
WHERE
KCU.TABLE_NAME = 'TABLE_NAME' AND
KCU.COLUMN_NAME = 'TABLE_COLUMN_NAME'
IF @ConstraintName IS NOT NULL EXEC('alter table TABLE_NAME drop CONSTRAINT ' + @ConstraintName)
它会根据特定的表和列删除外键约束。
