sql - 第二个检查约束是什么意思？

Question

我有以下代码

--first statement
ALTER TABLE [nameOfMyTable]  WITH CHECK ADD  CONSTRAINT [nameOfMyConstraint] FOREIGN KEY([myFK])
REFERENCES [tableReference] ([myFK])
GO
--second statement
ALTER TABLE [nameOfMyTable] CHECK CONSTRAINT [nameOfMyConstraint]
GO

首先，我在一个表上定义了一个 CHECK 约束。第二句话是什么意思？

Answer 1

第二条语句是多余的，唯一需要的时候是第一条语句有 WITH NOCHECK .默认 WITH CHECK如果您没有明确说明 CHECK或 NOCHECK在 ADD CONSTRAINT陈述。

sql server management studio generate this code by default – Mikhail

因为代码是自动生成的，所以它只是由一组步骤构造而成。其中一些步骤会有一些重叠，因此“表定义”步骤可能会在创建表时启用或禁用检查约束，但“设置约束”步骤也可能启用或禁用约束。

Relevant documentation :

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.

• 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.

• 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 WITH CHECK CHECK CONSTRAINT ALL.

{ CHECK | NOCHECK } CONSTRAINT

• Specifies that constraint_name is enabled or disabled. This option can only be used with FOREIGN KEY and CHECK constraints. When NOCHECK is specified, the constraint is disabled and future inserts or updates to the column are not validated against the constraint conditions. DEFAULT, PRIMARY KEY, and UNIQUE constraints cannot be disabled.