如何在Sql Server中检查约束是否存在？

Question

我有这个 SQL 查询：

ALTER TABLE dbo.ChannelPlayerSkins
    DROP CONSTRAINT FK_ChannelPlayerSkins_Channels

但是显然，在我们使用的一些其他数据库中，该约束具有不同的名称。如何检查是否存在名称为FK_ChannelPlayerSkins_Channels的约束。

- Lieven Cardoen

---

http://geekswithblogs.net/deadlydog/archive/2012/09/14/sql-server-script-commands-to-check-if-object-exists-and.aspx - gotqn

很多答案在同一约束名称用于多个对象或另一个模式时会失败。 - Mark Schultheiss

Answer 1

尝试这个：

SELECT
    * 
    FROM INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS 
    WHERE CONSTRAINT_NAME ='FK_ChannelPlayerSkins_Channels'

--编辑--

当我最初回答这个问题时，我想到的是“外键”，因为原始问题询问如何找到“FK_ChannelPlayerSkins_Channels”。自那以后，许多人已经评论了发现其他“约束条件”，以下是一些其他查询：

--Returns one row for each CHECK, UNIQUE, PRIMARY KEY, and/or FOREIGN KEY
SELECT * 
    FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS
    WHERE CONSTRAINT_NAME='XYZ'  


--Returns one row for each FOREIGN KEY constrain
SELECT * 
    FROM INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS 
    WHERE CONSTRAINT_NAME='XYZ'


--Returns one row for each CHECK constraint 
SELECT * 
    FROM INFORMATION_SCHEMA.CHECK_CONSTRAINTS
    WHERE CONSTRAINT_NAME='XYZ'

这里有一种替代方法

--Returns 1 row for each CHECK, UNIQUE, PRIMARY KEY, FOREIGN KEY, and/or DEFAULT
SELECT 
    OBJECT_NAME(OBJECT_ID) AS NameofConstraint
        ,SCHEMA_NAME(schema_id) AS SchemaName
        ,OBJECT_NAME(parent_object_id) AS TableName
        ,type_desc AS ConstraintType
    FROM sys.objects
    WHERE type_desc LIKE '%CONSTRAINT'
        AND OBJECT_NAME(OBJECT_ID)='XYZ'

如果您需要更多的约束信息，请查看系统存储过程master.sys.sp_helpconstraint中的内容，以了解如何获得某些信息。要使用SQL Server Management Studio查看源代码，请进入"对象资源管理器"。从那里，您展开“Master”数据库，然后展开“可编程性”，然后展开“存储过程”，然后展开“系统存储过程”。您可以找到“sys.sp_helpconstraint”，右键单击它并选择“修改”。只需小心不要保存任何更改即可。另外，您可以通过像EXEC sp_helpconstraint YourTableNameHere这样使用该系统存储过程在任何表上使用它。

- KM.

---

需要注意的一点是，在我的 SQL 语句中添加约束时，我使用了类似 "[fk_Client_ProjectID_Project]" 的名称括在括号中。但是，在 WHERE 子句中，您必须删除这些括号。 - ScubaSteve

括号里没有问题。这是一个 SQL Server 的问题，不是 MySQL 的问题。 - Álvaro González

如果您需要唯一约束，则需要稍微不同的版本： IF NOT EXISTS (SELECT 1 FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS where CONSTRAINT_NAME = 'UNIQUE_Order_ExternalReferenceId') BEGIN ALTER TABLE Order ADD CONSTRAINT UNIQUE_Order_ExternalReferenceId UNIQUE (ExternalReferenceId) END - The Coder

以上对于唯一列约束（SQL2008）不起作用。我必须使用以下语句：SELECT * FROM INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE WHERE CONSTRAINT_NAME='UC_constraintName' - Alan B. Dee

对于默认约束，只有列出的备用方法返回一行。 - ChargingPun

Answer 2

检查约束是否存在（然后执行特定操作，例如如果存在则删除它）的最简单方法是使用OBJECT_ID()函数...

IF OBJECT_ID('dbo.[CK_ConstraintName]', 'C') IS NOT NULL 
    ALTER TABLE dbo.[tablename] DROP CONSTRAINT CK_ConstraintName

OBJECT_ID可以不带第二个参数('C'仅用于检查约束)使用，这样也可能会起作用，但是如果您的约束名称与数据库中其他对象的名称匹配，可能会导致意外的结果。

IF OBJECT_ID('dbo.[CK_ConstraintName]') IS NOT NULL 
    ALTER TABLE dbo.[tablename] DROP CONSTRAINT CK_ConstraintName

OBJECT_ID也可以与其他“约束”一起使用，例如外键约束或主键约束等。为了获得最佳结果，请始终将适当的对象类型作为OBJECT_ID函数的第二个参数包含在内：

约束对象类型：

- C = CHECK 约束 - D = DEFAULT（约束或独立） - F = FOREIGN KEY 约束 - PK = PRIMARY KEY 约束 - R = Rule（旧式，独立） - UQ = UNIQUE 约束

此外，请注意通常需要模式。约束的模式通常是父表的模式。

如果在使用此方法时未将约束（或任何要检查的内容）放入括号中，还可能导致错误的负面结果--如果您的对象使用不寻常的字符（例如.），则需要括号。

- EricI

---

重要的是在OBJECT_ID函数的参数中添加模式名称，就像这样：IF OBJECT_ID('dbo.CK_ConstraintName', 'C') IS NOT NULL。如果不指定模式名称，它将返回NULL。 - gator88

嗨，谢谢你的回答，非常有帮助。只是想知道它是否适用于Oracle？ - user900202

不支持在 SQL2000 上运行。只需使用 OBJECTPROPERTY(OBJECT_ID('constraint_name'), 'IsConstraint') = 1 即可兼容当前版本到 SQL2000。也不需要 dbo 模式。 - wqw

Answer 3

如果你正在寻找其他类型的约束，例如默认值，请使用不同的查询方式 （来源于《如何使用INFORMATION_SCHEMA查找默认约束？》，由devio回答）。使用：

SELECT * FROM sys.objects WHERE type = 'D' AND name = @name

按名称查找默认约束。

我在我的帖子“DDL 'IF not Exists" conditions to make SQL scripts re-runnable"中，提供了不同的“如果不存在”检查。

- Michael Freidgeim

---

对于主键，类型应为“PK”，而不是“D”。 - Enrico

Answer 4

IF (OBJECT_ID('FK_ChannelPlayerSkins_Channels') IS NOT NULL)

- DevDave

---

结果发现我必须添加模式名称，请参阅https://dev59.com/7Inda4cB1Zd3GeqPDsSI。 - Johan Maes

Answer 5

你是不是在寻找像下面这样的东西，在 SQL Server 2005 中测试过：

SELECT * FROM sys.check_constraints WHERE 
object_id = OBJECT_ID(N'[dbo].[CK_accounts]') AND 
parent_object_id = OBJECT_ID(N'[dbo]. [accounts]')

- asyncwait

Answer 6

仅需要注意的一点是......

在SQL Server 2008 R2 SSMS中，“Script Constraint as -> DROP And CREATE To”命令会生成以下类似T-SQL的代码

USE [MyDatabase]
GO

IF  EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[DEF_Detail_IsDeleted]') AND type = 'D')
BEGIN
ALTER TABLE [Patient].[Detail] DROP CONSTRAINT [DEF_Detail_IsDeleted]
END

GO

USE [MyDatabase]
GO

ALTER TABLE [Patient].[Detail] ADD  CONSTRAINT [DEF_Detail_IsDeleted]  DEFAULT ((0)) FOR [IsDeleted]
GO

默认情况下，该脚本不会删除约束，因为SELECT语句返回0行。（请参见Microsoft Connect帖子）。

默认约束名称错误，但我认为这也与OBJECT_ID函数有关，因为更改名称无法解决问题。

为了解决此问题，我删除了OBJECT_ID的使用，并改用默认约束名称。

(SELECT * FROM dbo.sysobjects WHERE [name] = (N'DEF_Detail_IsDeleted') AND type = 'D')

- Mike1234

---

看起来脚本没有对名称进行模式限定。如果您在不同的模式中有两个同名约束，则最好使用OBJECT_ID(N'[YourSchema].[DEF_Detail_IsDeleted]')。 - Martin Smith

Answer 7

我使用以下查询来检查在创建之前是否存在约束。

IF (NOT EXISTS(SELECT 1 FROM sysconstraints WHERE OBJECT_NAME(constid) = 'UX_CONSTRAINT_NAME' AND OBJECT_NAME(id) = 'TABLE_NAME')) BEGIN
...
END

这会查询针对给定表名的约束名称。希望这能帮到您。

- Christo

Answer 8

IF EXISTS(SELECT TOP 1 1 FROM sys.default_constraints WHERE parent_object_id = OBJECT_ID(N'[dbo].[ChannelPlayerSkins]') AND name = 'FK_ChannelPlayerSkins_Channels')
BEGIN
    DROP CONSTRAINT FK_ChannelPlayerSkins_Channels
END
GO

- Tim Masters

---

外键（如原帖中的示例）似乎没有保存在默认约束中。 - Dodecaphone

Answer 9

IF EXISTS(SELECT 1 FROM sys.foreign_keys WHERE parent_object_id = OBJECT_ID(N'dbo.TableName'))
 BEGIN 
ALTER TABLE TableName DROP CONSTRAINT CONSTRAINTNAME 
END 

- StackOrder

Answer 10

从SQL Server 2016开始，您可以直接使用IF EXISTS关键字。

ALTER TABLE dbo.ChannelPlayerSkins
    DROP CONSTRAINT IF EXISTS FK_ChannelPlayerSkins_Channels

我正在使用SQL Server 2019，但是this提到它自SQL Server 2016起就可用。

SQL Server文档在ALTER TABLE页面下这里提到了它，而不是在this删除检查约束页面下。我不确定为什么。

IF EXISTS 适用于：SQL Server（SQL Server 2016（13.x）及更高版本）和 Azure SQL Database。 仅在列或约束已存在时有条件地删除它。

- shmuels