添加约束时不确定WITH CHECK子句 [英] Unsure of WITH CHECK clause when adding a constraint

问题描述

我正在尝试添加检查约束.

I am trying to add a check constraint.

我先做

ALTER TABLE [Production].[Products]  WITH CHECK 
ADD  CONSTRAINT [CHK_Products_unitprice] CHECK  (([unitprice]>=(0)))
GO

失败并显示消息

信息547，级别16，状态0，第1行
ALTER TABLE语句与CHECK约束"CHK_Products_unitprice"冲突.在数据库"TSQL2012"的表"Production.Products"的"unitprice"列中发生了冲突.

Msg 547, Level 16, State 0, Line 1
The ALTER TABLE statement conflicted with the CHECK constraint "CHK_Products_unitprice". The conflict occurred in database "TSQL2012", table "Production.Products", column 'unitprice'.

然后我尝试添加不带"WITH CHECK"选项的约束，它仍然会出现上述错误.

I then try adding the constraint without the "WITH CHECK" option and it still gives the above error.

那么有检查"选项的意义何在?

So what is the point of the "with check" option then?

推荐答案

创建新的检查约束的默认设置是WITH CHECK选项，该选项将读取和评估现有值，而导致ALTER失败.如果发现冲突.

The default for creating new check constraints is the WITH CHECK option that will read and evaluate existing values, failing the ALTER if conflicts are found.

如果需要部署检查约束而不考虑现有的现有不良值，并且了解不利之处(请参见下文)，则可以使用WITH NOCHECK选项，这是启用先前禁用的约束时的默认设置

You can use WITH NOCHECK option if you need to deploy a check constraint that disregards existing existing bad values and you understand the downsides (see below), which is the default when enabling previously disabled constraints.

有关详细信息，请参见 ALTER TABLE参考:

Please see the ALTER TABLE reference for details:

请检查|带有NOCHECK

WITH CHECK | WITH NOCHECK

指定是否验证表中的数据 新添加或重新启用的FOREIGN KEY或CHECK约束.如果不 如果指定，则对新约束使用WITH CHECK，而WITH NOCHECK 被假定为具有重新启用的约束条件.

Specifies whether the data in the table is or is not validated against a newly added or re-enabled FOREIGN KEY or CHECK constraint. If not specified, WITH CHECK is assumed for new constraints, and WITH NOCHECK is assumed for re-enabled constraints.

如果您不想验证新的CHECK或FOREIGN KEY约束 针对现有数据，请使用WITH NOCHECK.我们不建议这样做 除非在极少数情况下如此.新的约束将在中评估 所有以后的数据更新.被抑制的任何约束违规 添加约束时，通过WITH NOCHECK可以导致将来的更新 如果他们用不符合要求的数据更新行，则会失败 约束.

If you do not want to verify new CHECK or FOREIGN KEY constraints against existing data, use WITH NOCHECK. We do not recommend doing this, except in rare cases. The new constraint will be evaluated in all later data updates. Any constraint violations that are suppressed by WITH NOCHECK when the constraint is added may cause future updates to fail if they update rows with data that does not comply with the constraint.

查询优化器不考虑已定义的约束 带有NOCHECK.此类约束将被忽略，直到重新启用它们 通过使用带有CHECK CHECK CONSTRAINT ALL的ALTER TABLE表.

The query optimizer does not consider constraints that are defined WITH NOCHECK. Such constraints are ignored until they are re-enabled by using ALTER TABLE table WITH CHECK CHECK CONSTRAINT ALL.

这篇关于添加约束时不确定WITH CHECK子句的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！