仅当 sql server 中存在外键约束时，如何删除它? [英] How do I drop a foreign key constraint only if it exists in sql server?

问题描述

如果表存在，我可以使用以下代码删除它，但不知道如何使用约束来做同样的事情:

I can drop a table if it exists using the following code but do not know how to do the same with a constraint:

IF EXISTS(SELECT 1 FROM sys.objects WHERE OBJECT_ID = OBJECT_ID(N'TableName') AND type = (N'U')) DROP TABLE TableName
go 

我还使用以下代码添加了约束:

I also add the constraint using this code:

ALTER TABLE [dbo].[TableName] 
  WITH CHECK ADD CONSTRAINT [FK_TableName_TableName2] FOREIGN KEY([FK_Name])
    REFERENCES [dbo].[TableName2] ([ID])
go

推荐答案

Eric Isaacs 的回答 中提供了更简单的解决方案.但是，它会在任何表上找到约束.如果要在特定表上定位外键约束，请使用:

The more simple solution is provided in Eric Isaacs's answer. However, it will find constraints on any table. If you want to target a foreign key constraint on a specific table, use this:

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]

这篇关于仅当 sql server 中存在外键约束时，如何删除它?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！