Why WITH CHECK CHECK CONSTRAINT is Used For?

CHECK constraint SQL Server The CHECK constraint is used to limit the value range that can be placed in a column. If you define a CHECK constraint on a column it will allow only certain values for this column. If you define a CHECK constraint on a table it can limit the values in certain columns based on values in other columns in the row. CHECK CHECK is used in the syntax for enabling a check constraint in SQL Server (Transact-SQL) is ALTER TABLE table_name WITH CHECK CHECK CONSTRAINT constraint_name; table_name. The name of the table that you wish to enable the check constraint.

The MSDN documentation 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 constraint
NOCHECK CONSTRAINT ..: disable the constraint
There are also additional, optional steps to do while creating/enabling/disabling a constraint:
WITH CHECK: check the constraint as well
WITH 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-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 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.

Test in dbfiddle:

  CREATE TABLE an (aid INT PRIMARY KEY); GO ✓INSERT INTO an (aid) VALUES (1), (2), (3) ; GO3 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 affectedINSERT INTO b (aid, bid) VALUES (6, 61), (6, 62) ; GO Msg 547 Level 16 State 0 Line 1The 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"