我可以删除一个表，如果它存在使用以下代码，但不知道如何做同样的约束:
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
当前回答
好吧，我知道我迟到了，但是下面是我认为最好的语法。如果需要，向OBJECT_ID子句添加模式名。
IF OBJECTPROPERTY(OBJECT_ID(N'My_FK_name'),'IsConstraint') =1
ALTER TABLE dbo.TableName DROP CONSTRAINT My_FK_name
其他回答
IF (OBJECT_ID('DF_Constraint') IS NOT NULL)
BEGIN
ALTER TABLE [dbo].[tableName]
DROP CONSTRAINT DF_Constraint
END
对于这个问题，公认的答案似乎对我不起作用。我用稍微不同的方法达到了同样的效果:
IF (select object_id from sys.foreign_keys where [name] = 'FK_TableName_TableName2') IS NOT NULL
BEGIN
ALTER TABLE dbo.TableName DROP CONSTRAINT FK_TableName_TableName2
END
埃里克·艾萨克斯给出了更简单的答案。但是，它可以在任何表上找到约束。如果你想在一个特定的表上指定一个外键约束，使用这个:
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]
您可以使用这些查询为您的表查找所有fk。
Declare @SchemaName VarChar(200) = 'Schema Name'
Declare @TableName VarChar(200) = 'Table name'
-- Find FK in This table.
SELECT
'IF EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N''' +
'[' + OBJECT_SCHEMA_NAME(FK.parent_object_id) + '].[' + FK.name + ']'
+ ''') AND parent_object_id = OBJECT_ID(N''' +
'[' + OBJECT_SCHEMA_NAME(FK.parent_object_id) + '].['
+ OBJECT_NAME(FK.parent_object_id) + ']' + ''')) ' +
'ALTER TABLE ' + OBJECT_SCHEMA_NAME(FK.parent_object_id) +
'.[' + OBJECT_NAME(FK.parent_object_id) +
'] DROP CONSTRAINT ' + FK.name
, S.name , O.name, OBJECT_NAME(FK.parent_object_id)
FROM sys.foreign_keys AS FK
INNER JOIN Sys.objects As O
ON (O.object_id = FK.parent_object_id )
INNER JOIN SYS.schemas AS S
ON (O.schema_id = S.schema_id)
WHERE
O.name = @TableName
And S.name = @SchemaName
-- Find the FKs in the tables in which this table is used
SELECT
' IF EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N''' +
'[' + OBJECT_SCHEMA_NAME(FK.parent_object_id) + '].[' + FK.name + ']'
+ ''') AND parent_object_id = OBJECT_ID(N''' +
'[' + OBJECT_SCHEMA_NAME(FK.parent_object_id) + '].['
+ OBJECT_NAME(FK.parent_object_id) + ']' + ''')) ' +
' ALTER TABLE ' + OBJECT_SCHEMA_NAME(FK.parent_object_id) +
'.[' + OBJECT_NAME(FK.parent_object_id) +
'] DROP CONSTRAINT ' + FK.name
, S.name , O.name, OBJECT_NAME(FK.parent_object_id)
FROM sys.foreign_keys AS FK
INNER JOIN Sys.objects As O
ON (O.object_id = FK.referenced_object_id )
INNER JOIN SYS.schemas AS S
ON (O.schema_id = S.schema_id)
WHERE
O.name = @TableName
And S.name = @SchemaName
好吧，我知道我迟到了，但是下面是我认为最好的语法。如果需要，向OBJECT_ID子句添加模式名。
IF OBJECTPROPERTY(OBJECT_ID(N'My_FK_name'),'IsConstraint') =1
ALTER TABLE dbo.TableName DROP CONSTRAINT My_FK_name
