What is a WITH CHECK CHECK CONSTRAINT?
The MSDN documentattion page about ALTER TABLE
explains these:
ALTER TABLE
: modify the table's structure
(and some of the possible actions/modifications are):CHECK CONSTRAINT ..
: enable the constraintNOCHECK CONSTRAINT ..
: disable the constraint
There are also additional, optional steps to do while creating/enabling/disabling a constraint:WITH CHECK
: check the constraint as wellWITH NOCHECK
: do not check the constraint
In their words:
| [ WITH { CHECK | NOCHECK } ] { CHECK | NOCHECK } CONSTRAINT { ALL | constraint_name [ ,...n ] }
...
WITH CHECK | WITH NOCHECK
Specifies whether the data in the table is or is not validated against a newly added or re-enabledFOREIGN KEY
orCHECK
constraint. If not specified,WITH CHECK
is assumed for new constraints, andWITH NOCHECK
is assumed for re-enabled constraints.If you do not want to verify new
CHECK
orFOREIGN KEY
constraints against existing data, useWITH 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 byWITH 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 usingALTER TABLE
tableWITH CHECK CHECK CONSTRAINT ALL
....
{ CHECK | NOCHECK } CONSTRAINT
Specifies that constraint_name is enabled or disabled. This option can only be used withFOREIGN KEY
andCHECK
constraints. WhenNOCHECK
is specified, the constraint is disabled and future inserts or updates to the column are not validated against the constraint conditions.DEFAULT
,PRIMARY KEY
, andUNIQUE
constraints cannot be disabled.
Test in dbfiddle:
CREATE TABLE a (aid INT PRIMARY KEY);
GO
✓
INSERT INTO a (aid) VALUES (1), (2), (3) ;
GO
3 rows affected
CREATE TABLE b ( aid INT, bid INT PRIMARY KEY, CONSTRAINT [My_FORIEGN_KEY] FOREIGN KEY (aid) REFERENCES a (aid) ) ;
GO
✓
INSERT INTO b (aid, bid) VALUES (1, 11), (1, 12), (2, 21), (3, 31) ;
GO
4 rows affected
INSERT INTO b (aid, bid) VALUES (6, 61), (6, 62) ;
GO
Msg 547 Level 16 State 0 Line 1 The INSERT statement conflicted with the FOREIGN KEY constraint "My_FORIEGN_KEY". The conflict occurred in database "fiddle_792fce5de09f42908c3a0f91421f3522", table "dbo.a", column 'aid'. Msg 3621 Level 0 State 0 Line 1 The statement has been terminated.
SELECT * FROM b ;
GO
aid | bid --: | --: 1 | 11 1 | 12 2 | 21 3 | 31
ALTER TABLE b NOCHECK CONSTRAINT [My_FORIEGN_KEY]; --disable
GO
✓
INSERT INTO b (aid, bid) VALUES (4, 41), (4, 42) ;
GO
2 rows affected
SELECT * FROM b ;
GO
aid | bid --: | --: 1 | 11 1 | 12 2 | 21 3 | 31 4 | 41 4 | 42
ALTER TABLE b WITH NOCHECK CHECK CONSTRAINT [My_FORIEGN_KEY]; -- enable constraint without checking existing data
GO
✓
SELECT * FROM b ;
GO
aid | bid --: | --: 1 | 11 1 | 12 2 | 21 3 | 31 4 | 41 4 | 42
INSERT INTO b (aid, bid) VALUES (6, 61), (6, 62) ;
GO
Msg 547 Level 16 State 0 Line 1 The INSERT statement conflicted with the FOREIGN KEY constraint "My_FORIEGN_KEY". The conflict occurred in database "fiddle_792fce5de09f42908c3a0f91421f3522", table "dbo.a", column 'aid'. Msg 3621 Level 0 State 0 Line 1 The statement has been terminated.
SELECT * FROM b ;
GO
aid | bid --: | --: 1 | 11 1 | 12 2 | 21 3 | 31 4 | 41 4 | 42
ALTER TABLE b WITH CHECK CHECK CONSTRAINT [My_FORIEGN_KEY]; -- check existing data and enable constraint
GO
Msg 547 Level 16 State 0 Line 1 The ALTER TABLE statement conflicted with the FOREIGN KEY constraint "My_FORIEGN_KEY". The conflict occurred in database "fiddle_792fce5de09f42908c3a0f91421f3522", table "dbo.a", column 'aid'.