如何检查Sql server中是否存在约束?

I have this sql:

我有这个sql:

ALTER TABLE dbo.ChannelPlayerSkins
    DROP CONSTRAINT FK_ChannelPlayerSkins_Channels

but apparently, on some other databases we use, the constraint has a different name. How do I check if there's a constraint with the name FK_ChannelPlayerSkins_Channels.

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

13 个解决方案

#1

try this:

试试这个:

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

-- EDIT --

——编辑

When I originally answered this question, I was thinking "Foreign Key" because the original question asked about finding "FK_ChannelPlayerSkins_Channels". Since then many people have commented on finding other "constraints" here are some other queries for that:

当我最初回答这个问题时，我想的是“外键”，因为最初的问题是关于寻找“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'

here is an alternate method

这是另一种方法。

--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'

If you need even more constraint information, look inside the system stored procedure master.sys.sp_helpconstraint to see how to get certain information. To view the source code using SQL Server Management Studio get into the "Object Explorer". From there you expand the "Master" database, then expand "Programmability", then "Stored Procedures", then "System Stored Procedures". You can then find "sys.sp_helpconstraint" and right click it and select "modify". Just be careful to not save any changes to it. Also, you can just use this system stored procedure on any table by using it like EXEC sp_helpconstraint YourTableNameHere.

如果您需要更多的约束信息，请查看系统存储过程master.sys。sp_helpconstraint以查看如何获得某些信息。使用SQL Server Management Studio查看源代码，可以进入“对象资源管理器”。从这里扩展“主”数据库，然后扩展“可编程性”，然后“存储过程”，然后是“系统存储过程”。然后你就可以找到“sys”。“sp_helpconstraint”和“右击”并选择“modify”。只是要注意不要保存任何更改。另外，您可以在任何表上使用该系统存储过程，如EXEC sp_helpconstraint YourTableNameHere。

#2

Easiest way to check for the existence of a constraint (and then do something such as drop it if it exists) is to use the OBJECT_ID() function...

检查约束是否存在的最简单方法是使用OBJECT_ID()函数，然后执行一些操作，比如删除它。

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

OBJECT_ID can be used without the second parameter ('C' for check constraints only) and that may also work, but if your constraint name matches the name of other objects in the database you may get unexpected results.

OBJECT_ID可以在没有第二个参数(仅用于检查约束)的情况下使用，也可以使用，但是如果您的约束名称与数据库中其他对象的名称相匹配，您可能会得到意想不到的结果。

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

OBJECT_ID can also be used with other "constraints" such as Foreign Key constraints or Primary Key constraints, etc. For best results, always include the appropriate object type as the second parameter for the OBJECT_ID function:

OBJECT_ID还可以与其他“约束”(如外键约束或主键约束等)一起使用，为了获得最佳结果，总是将适当的对象类型作为OBJECT_ID函数的第二个参数:

Constraint Object Types:

约束对象类型:

• C = CHECK constraint
• C =检查约束
• D = DEFAULT (constraint or stand-alone)
• D =默认值(约束或独立)
• F = FOREIGN KEY constraint
• F =外键约束。
• PK = PRIMARY KEY constraint
• PK =主键约束。
• R = Rule (old-style, stand-alone)
• R =规则(旧式的，独立的)
• UQ = UNIQUE constraint
• UQ =唯一约束

Also note that the schema is often required. The schema of constraints generally takes the schema of the parent table.

还要注意，通常需要模式。约束的模式通常采用父表的模式。

Failure to put your constraints (or whatever you are checking) in brackets when using this method may also cause a false negative -- if your object uses unusual characters (such as a .), the brackets are required.

如果在使用此方法时没有将约束(或任何您正在检查的内容)放在括号中，也可能导致错误的错误——如果您的对象使用了不寻常的字符(例如a)，则需要使用括号。

#3

If you are looking for other type of constraint, e.g. defaults, you should use different query (From How do I find a default constraint using INFORMATION_SCHEMA? answered by devio). Use:

如果您正在寻找其他类型的约束，例如默认值，您应该使用不同的查询(从如何使用INFORMATION_SCHEMA找到一个默认的约束?回答devio)。使用:

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

to find a default constraint by name.

按名称查找默认约束。

I've put together different 'IF not Exists" checks in my post "DDL 'IF not Exists" conditions to make SQL scripts re-runnable"

我已经将不同的“如果不存在”的检查放在了我的post“DDL”中，如果不存在的话，可以让SQL脚本重新运行。

#4

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

#5

Are you looking at something like this, below is tested in SQL Server 2005

您是否正在查看类似这样的东西，下面是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]')

#6

Just something to watch out for......

只是需要注意的事情……

In SQL Server 2008 R2 SSMS, the "Script Constraint as -> DROP And CREATE To" command produces T-SQL like below

在SQL Server 2008 R2 SSMS中，“脚本约束as -> DROP和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

Out of the box, this script does NOT drop the constraint because the SELECT returns 0 rows. (see post Microsoft Connect).

从这个框中，这个脚本不会删除约束，因为SELECT返回0行。(见后微软连接)。

The name of the default constraint is wrong but I gather it also has something to do with the OBJECT_ID function because changing the name doesn't fix the problem.

默认约束的名称是错误的，但是我收集它也与OBJECT_ID函数有关，因为更改名称并不能解决问题。

To fix this, I removed the usage of OBJECT_ID and used the default constraint name instead.

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

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

#7

I use the following query to check for an existing constraint before I create it.

在创建之前，我使用下面的查询来检查现有的约束。

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

This queries for the constraint by name targeting a given table name. Hope this helps.

此查询用于针对给定表名的名称进行约束。希望这个有帮助。

#8

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 

#9

INFORMATION_SCHEMA is your friend. It has all kinds of views that show all kinds of schema information. Check your system views. You will find you have three views dealing with constraints, one being CHECK_CONSTRAINTS.

INFORMATION_SCHEMA是你的朋友。它有各种各样的视图，显示各种模式信息。检查你的系统视图。您将发现您有三个处理约束的视图，一个是CHECK_CONSTRAINTS。

#10

I use this to check for and remote constraints on a column. It should have everything you need.

我使用它来检查列上的远程约束。它应该有你需要的一切。

DECLARE
  @ps_TableName VARCHAR(300)
  , @ps_ColumnName VARCHAR(300)

SET @ps_TableName = 'mytable'
SET @ps_ColumnName = 'mycolumn'

DECLARE c_ConsList CURSOR LOCAL STATIC FORWARD_ONLY FOR
    SELECT
    'ALTER TABLE ' + RTRIM(tb.name) + ' drop constraint ' + sco.name AS csql
    FROM
        sys.Objects tb
        INNER JOIN sys.Columns tc on (tb.Object_id = tc.object_id)
        INNER JOIN sys.sysconstraints sc ON (tc.Object_ID = sc.id and tc.column_id = sc.colid)
        INNER JOIN sys.objects sco ON (sc.Constid = sco.object_id)
    where
        tb.name=@ps_TableName
        AND tc.name=@ps_ColumnName
OPEN c_ConsList
FETCH c_ConsList INTO @ls_SQL
WHILE (@@FETCH_STATUS = 0) BEGIN

    IF RTRIM(ISNULL(@ls_SQL, '')) <> '' BEGIN
        EXECUTE(@ls_SQL)
    END
    FETCH c_ConsList INTO @ls_SQL
END
CLOSE c_ConsList
DEALLOCATE c_ConsList

#11

SELECT tabla.name as Tabla,

        restriccion.name as Restriccion, 
        restriccion.type as Tipo, 
        restriccion.type_desc as Tipo_Desc
FROM {DATABASE_NAME}.sys.objects tabla 

INNER JOIN {DATABASE_NAME}.sys.objects restriccion

ON tabla.object_id = restriccion.parent_object_id

WHERE tabla.type = 'U' - Solo tablas creadas por el usuario.

AND restriccion.type = 'UQ' --Tipo de Restriccion UNIQUE

ORDER BY tabla.name, restriccion.type_desc                

#12

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

#13

You can use the one above with one caveat:

你可以使用上面的一个警告:

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

Need to use the name = [Constraint name] since a table may have multiple foreign keys and still not have the foreign key being checked for

需要使用name =[约束名称]，因为表可能有多个外键，并且仍然没有检查外键。