我可以删除一个表，如果它存在使用以下代码，但不知道如何做同样的约束:
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
当前回答
所有表约束都存储在INFORMATION_SCHEMA中。TABLE_CONSTRAINTS
IF EXISTS(SELECT * FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS AS C WHERE C.CONSTRAINT_NAME = '<CONSTRAINT NAME>' AND C.TABLE_NAME = '<TABLE NAME>')
BEGIN
ALTER TABLE <TABLE NAME>
DROP CONSTRAINT <CONSTRAINT NAME>
END
GO
其他回答
Declare @FKeyRemoveQuery NVarchar(max)
IF EXISTS(SELECT 1 FROM sys.foreign_keys WHERE parent_object_id = OBJECT_ID(N'dbo.TableName'))
BEGIN
SELECT @FKeyRemoveQuery='ALTER TABLE dbo.TableName DROP CONSTRAINT [' + LTRIM(RTRIM([name])) + ']'
FROM sys.foreign_keys
WHERE parent_object_id = OBJECT_ID(N'dbo.TableName')
EXECUTE Sp_executesql @FKeyRemoveQuery
END
好吧，我知道我迟到了，但是下面是我认为最好的语法。如果需要，向OBJECT_ID子句添加模式名。
IF OBJECTPROPERTY(OBJECT_ID(N'My_FK_name'),'IsConstraint') =1
ALTER TABLE dbo.TableName DROP CONSTRAINT My_FK_name
埃里克·艾萨克斯给出了更简单的答案。但是，它可以在任何表上找到约束。如果你想在一个特定的表上指定一个外键约束，使用这个:
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]
所有表约束都存储在INFORMATION_SCHEMA中。TABLE_CONSTRAINTS
IF EXISTS(SELECT * FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS AS C WHERE C.CONSTRAINT_NAME = '<CONSTRAINT NAME>' AND C.TABLE_NAME = '<TABLE NAME>')
BEGIN
ALTER TABLE <TABLE NAME>
DROP CONSTRAINT <CONSTRAINT NAME>
END
GO
这在Azure Sql服务器上工作得很顺利:
IF (OBJECT_ID('dbo.FK_company_id', 'F') IS NOT NULL)
BEGIN
ALTER TABLE dbo.table_company DROP CONSTRAINT FK_company_id
END
