SQL Server:如何使服务器检查其所有检查约束? [英] SQL Server: How to make server check all its check constraints?

问题描述

似乎由企业管理器*(或无关紧要的)生成的某些脚本创建了检查约束没有检查.

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

现在，当有人修改表时， SQL服务器在失败的检查约束中绊脚石，并抛出错误.

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

我可以让SQL遍历所有检查约束，然后检查它们吗?

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

运行:

sp_msforeachtable 'ALTER TABLE ? CHECK CONSTRAINT all'

仅启用先前禁用的检查约束，而实际上不检查它们.

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

* SQL Server 2000

* SQL Server 2000

推荐答案

具有ALL_CONSTRAINTS的DBCC检查约束实际上不会使您的约束受到信任.它将报告违反约束的任何行.要使所有约束真正得到信任，可以执行以下操作:

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:

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

在SQL Server 2000中，您可以通过以下方式找到任何不受信任的约束:

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

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

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

注意:在最后一条语句中，WITH CHECK CHECK不是错字. "WITH CHECK"将检查所有表数据以确保没有违规，并使约束成为可信任的，而检查将确保启用了约束.

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.

另请参阅: 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

这篇关于SQL Server:如何使服务器检查其所有检查约束?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！