SQL Server：如何使服务器检查其所有检查约束？

It seems that some scripts generated by Enterprise Manager* (or not, it doesn't matter) created check constraints WITH NOCHECK.

似乎企业管理器*生成的一些脚本（或者没有，无关紧要）创建了检查约束WITH NOCHECK。

Now when anyone modifies the table, SQL Server is stumbling across failed check constraints, and throwing errors.

现在，当任何人修改表时，SQL Server都会遇到失败的检查约束，并抛出错误。

Can i make SQL go through all its check constraints, and check them?

我可以让SQL通过它的所有检查约束，并检查它们吗？

Running:

运行：

sp_msforeachtable 'ALTER TABLE ? CHECK CONSTRAINT all'

only enables previously disabled check constraints, it doesn't actually check them.

只启用以前禁用的检查约束，它实际上不会检查它们。

Footnotes

* SQL Server 2000

3 个解决方案

#1

DBCC CHECKCONSTRAINTS WITH ALL_CONSTRAINTS won't actually make your constraints trusted. It will report any rows that violate the constraints. To actually make all of your constraints trusted, you can do the following:

带有ALL_CONSTRAINTS的DBCC CHECKCONSTRAINTS实际上不会使您的约束受信任。它将报告违反约束的任何行。要实际使所有约束受信任，您可以执行以下操作：

DBCC CHECKCONSTRAINTS WITH ALL_CONSTRAINTS --This reports any data that violates constraints.

--This reports all constraints that are not trusted
SELECT OBJECT_NAME(parent_object_id) AS table_name, name, is_disabled  
FROM sys.check_constraints 
WHERE is_not_trusted = 1
UNION ALL
SELECT OBJECT_NAME(parent_object_id) AS table_name, name, is_disabled  
FROM sys.foreign_keys
WHERE is_not_trusted = 1
ORDER BY table_name

In SQL Server 2000 you can find any untrusted constraints with:

在SQL Server 2000中，您可以找到任何不受信任的约束：

--Reports all constraints that are not trusted (SQL 2000)
SELECT name, type, status,
    (status & 2048) AS IsTrusted,
    (status & 256) AS IsEnabled,
    OBJECTPROPERTY(id,'CnstIsNotTrusted') as is_not_trusted,
    OBJECTPROPERTY(id,'CnstIsDisabled') as is_disabled
FROM sysobjects 
WHERE type IN ('C', 'F') --C=Constraint, F=Foreign Key
AND OBJECTPROPERTY(id,'CnstIsNotTrusted') <> 0
AND OBJECTPROPERTY(id,'CnstIsDisabled') = 0

Constraints are then re-reenabled with check:

然后使用check重新启用约束：

--This makes all constraints trusted
-- but first anything reported by DBCC CHECKCONSTRAINTS WITH ALL_CONSTRAINTS must be fixed.
exec sp_msforeachtable 'ALTER TABLE ? WITH CHECK CHECK CONSTRAINT all'

Note: on the last statement, the WITH CHECK CHECK is not a typo. The "WITH CHECK" will check all table data to ensure there are not violations, and will make the constraint trusted, while the check will make sure the constraints is enabled.

注意：在最后一个语句中，WITH CHECK CHECK不是拼写错误。 “WITH CHECK”将检查所有表数据以确保没有违规，并且将使约束受信任，而检查将确保启用约束。

另见：http：//sqlblog.com/blogs/tibor_karaszi/archive/2008/01/12/non-trusted-constraints.aspx

http://sqlblog.com/blogs/tibor_karaszi/archive/2008/01/12/non-trusted-constraints-and-performance.aspx

#2

Found it:

找到了：

Checks all constraints on all tables in the current database, whether the constraint is enabled or not:

检查当前数据库中所有表的所有约束，无论是否启用约束：

DBCC CHECKCONSTRAINTS WITH ALL_CONSTRAINTS

To check only enabled constraints:

要仅检查已启用的约束：

DBCC CHECKCONSTRAINTS

#3

do this:

做这个：

ALTER TABLE dbo.Test
      WITH CHECK CHECK CONSTRAINT CK_Test;

Explanation: Can you trust your constraints?

说明：你能相信你的约束吗？

#1