What does second check constraint mean?

Question

I have the following code

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

First, i define a CHECK constraint on a table. What does mean second statement?

Answer 1

The 2nd statement is redundant, the only time it would be needed is if the first statement had WITH NOCHECK. By default WITH CHECK is added if you don't explicitly state CHECK or NOCHECK in the ADD CONSTRAINT statement.

sql server management studio generate this code by default – Mikhail

Because the code is being auto generated it is just being constructed by a set of steps. Some of those steps will have some overlap so the "table definition" step may enable or disable the check the constraint while it creates the table, but the "setup constraints" step may also enable or disable the constraint.

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.

Answer 2

From the docs:

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.

Answer 3

The second statement in the given context is redundant if run immediately after the creation of the constraint (with out without WITH CHECK, creation of the foreign key constraint with ADD CONSTRAINT FOREIGN KEY will do the WITH CHECK immediately by default).

The second statement is used to reenable constraint checking

ALTER TABLE [nameOfMyTable] CHECK CONSTRAINT [nameOfMyConstraint];

usually after it has been disabled, like so:

ALTER TABLE [nameOfMyTable] NOCHECK CONSTRAINT [nameOfMyConstraint];
GO

Scripting tools often create DDL like this - overkill, although I guess they really want to be sure :)

There is a third flavour, which is to re-check the validity of the constraint, e.g. after doing a Bulk Copy or similar which may have invalidated the constraint (marked it as untrusted). This is done like so:

ALTER TABLE [nameOfMyTable] WITH CHECK CHECK CONSTRAINT [nameOfMyConstraint];

Edit Hopefully this SQLFiddle clears this up?