NOCHECK CONSTRAINT: Loading Data and Trusting Foreign Keys Again

Rejecting new bad rows does not prove that a foreign key trusts existing data. Understanding NOCHECK CONSTRAINT prevents a bulk-load shortcut from leaving that incomplete state behind.

Separate Enforcement from Trust

Enforcement determines whether the constraint checks relevant new data modifications. Trust records whether existing data has been validated against the constraint. These are related properties, but they are not interchangeable.

Disabling a foreign key removes its protection during the disabled interval. Re-enabling it without validating existing rows leaves that interval unexplained. SQL Server cannot assume all stored rows obey a rule that was not verified.

I check both properties after a load instead of accepting a successful ALTER TABLE statement as completion. I also preserve the load's validation evidence. A green completion message cannot interrogate an orphaned row hiding in yesterday's batch.

Disabling constraints does not make data correct or guarantee a faster load. Index maintenance, logging, locking, and source processing can still dominate the work. Measure the actual load and validation cost before choosing this approach.

Build a Foreign-Key Example

Run the example in a separate test database. The tables use an ordinary parent key and a child reference. The foreign key starts enabled and trusted because SQL Server validates its creation normally.

DROP TABLE IF EXISTS dbo.LoadChild;
DROP TABLE IF EXISTS dbo.LoadParent;
CREATE TABLE dbo.LoadParent
(
    ParentId int NOT NULL PRIMARY KEY
);
CREATE TABLE dbo.LoadChild
(
    ChildId int NOT NULL PRIMARY KEY,
    ParentId int NOT NULL,
    CONSTRAINT FK_LoadChild_Parent
        FOREIGN KEY (ParentId) REFERENCES dbo.LoadParent(ParentId)
);
INSERT dbo.LoadParent(ParentId) VALUES (1);
INSERT dbo.LoadChild(ChildId, ParentId) VALUES (10, 1);

The parent table establishes which identifiers exist. The child table must reference one of those identifiers under the active rule. A normal insert with ParentId equal to 999 would fail at this point.

Use a named constraint throughout the demonstration. Broad commands affecting every constraint can conceal unrelated changes. In production, record the original enabled and trusted state before any approved load preparation.

Nullable foreign-key columns need separate interpretation. A NULL child reference can satisfy the foreign key without finding a parent. This demonstration uses NOT NULL so every child reference must match an actual parent.

Run NOCHECK CONSTRAINT and Observe the Consequence

The following NOCHECK CONSTRAINT command stops enforcement for the named foreign key. The subsequent insert deliberately creates an invalid relationship. It demonstrates the risk rather than recommending the data change.

ALTER TABLE dbo.LoadChild
    NOCHECK CONSTRAINT FK_LoadChild_Parent;
INSERT dbo.LoadChild(ChildId, ParentId) VALUES (11, 999);

SELECT name, is_disabled, is_not_trusted
FROM sys.foreign_keys
WHERE parent_object_id = OBJECT_ID(N'dbo.LoadChild');

During a disabled interval, another writer can also introduce invalid rows. Restrict the maintenance window and coordinate application writes if this technique is required. Validation must cover every relevant row, not only the loader's own inserted rows.

Child updates and parent-side changes need review as part of that window. The disabled relationship no longer supplies the same protection. Loading parents first while keeping constraints enabled is a simpler alternative when the source allows it.

Keep source counts and rejection rules with the load record. A successful insert count says nothing about whether every reference exists. Treat relation validity as a separate required result.

Re-Enable after NOCHECK CONSTRAINT without Skipping Validation

CHECK CONSTRAINT turns enforcement back on, but does not automatically establish trust after disabling. WITH NOCHECK makes the absence of existing-row validation explicit. The following command therefore allows the old invalid row to remain. Its second insert is meant to fail, and the CATCH block shows error 547.

ALTER TABLE dbo.LoadChild
    WITH NOCHECK CHECK CONSTRAINT FK_LoadChild_Parent;

SELECT name, is_disabled, is_not_trusted
FROM sys.foreign_keys
WHERE parent_object_id = OBJECT_ID(N'dbo.LoadChild');

BEGIN TRY
    INSERT dbo.LoadChild(ChildId, ParentId) VALUES (12, 999);
END TRY
BEGIN CATCH
    SELECT ERROR_NUMBER() AS ErrorNumber,
           ERROR_MESSAGE() AS ErrorMessage;
END CATCH;

The new invalid insert is rejected because enforcement is active again. The previously inserted invalid row remains because it was not checked. This is the distinction between enabled and trusted in concrete form.

A plain CHECK CONSTRAINT after disabling produces the same important trust concern. Do not describe it as a full repair merely because future writes fail correctly. Read the catalog properties and inspect existing relationships.

WITH CHECK and CHECK occupy different positions in the repair syntax. The first requests validation of existing rows, while the second enables the constraint. The repeated word is meaningful rather than a typing accident.

Find Violations before Restoring Trust

An anti-join identifies child references without a matching parent. For a nullable foreign key, explicitly exclude NULL references from this diagnostic. Additional or composite key columns must participate in the full comparison. The validation attempt in the next block is meant to fail with error 547 while the orphan exists.

SELECT c.ChildId, c.ParentId
FROM dbo.LoadChild AS c
WHERE NOT EXISTS
(
    SELECT 1 FROM dbo.LoadParent AS p
    WHERE p.ParentId = c.ParentId
);

BEGIN TRY
    ALTER TABLE dbo.LoadChild
        WITH CHECK CHECK CONSTRAINT FK_LoadChild_Parent;
END TRY
BEGIN CATCH
    SELECT ERROR_NUMBER() AS ErrorNumber,
           ERROR_MESSAGE() AS ErrorMessage;
END CATCH;

Validation fails while the demonstrated orphan exists. The foreign key does not become trusted merely because validation was attempted. Preserve the error and correct the relationship using the approved source of truth.

Production correction can mean loading a legitimately missing parent, correcting the reference, or rejecting the child. Do not fabricate a parent row solely to satisfy the key. Deletion requires the appropriate business decision and evidence.

The following removes only the deliberately invalid demonstration row. It then validates all remaining relationships and enables the named foreign key. Read back the state after the successful statement.

DELETE dbo.LoadChild WHERE ChildId = 11 AND ParentId = 999;
ALTER TABLE dbo.LoadChild
    WITH CHECK CHECK CONSTRAINT FK_LoadChild_Parent;

SELECT name, is_disabled, is_not_trusted
FROM sys.foreign_keys
WHERE parent_object_id = OBJECT_ID(N'dbo.LoadChild');

Validation can scan substantial data and require locks. Plan its duration and resource demand as part of the load, not as an optional later task. A faster initial insert can be outweighed by expensive deferred validation.

Explain the Optimizer Consequence and Close the Load

Trusted constraints provide facts the optimizer can use when simplifying eligible queries. For example, a trusted relationship can support removing an unnecessary join in an appropriate query shape. An untrusted relationship cannot supply the same assumption about existing rows.

This does not guarantee that trusting a key removes every join or improves every plan. Query shape, nullability, selected columns, and other constraints matter. Inspect the relevant plan after restoring correctness rather than promising a fixed performance improvement.

Are every intended foreign key and check constraint both enabled and trusted after the load? Inventory their properties rather than checking one convenient table. Check constraints have corresponding trust and enabled-state metadata in sys.check_constraints.

SELECT SCHEMA_NAME(t.schema_id) AS SchemaName,
       t.name AS TableName, fk.name AS ConstraintName,
       fk.is_disabled, fk.is_not_trusted
FROM sys.foreign_keys AS fk
JOIN sys.tables AS t ON t.object_id = fk.parent_object_id
WHERE fk.is_disabled = 1 OR fk.is_not_trusted = 1
ORDER BY SchemaName, TableName, ConstraintName;

A transaction can group load and constraint-state changes, but its locking and log footprint require planning. Test rollback behavior with the actual loading method. Do not wrap an unlimited load in one transaction merely to conceal the disabled interval.

Retain a final inventory with the table, constraint name, enabled state, and trust state. Compare it with the recorded starting inventory. This exposes unintended changes and makes the load's completion review repeatable.

Preserve constraints that were intentionally disabled beforehand instead of blindly enabling everything. Close the load with approved data corrections and verified final states. NOCHECK CONSTRAINT is a temporary maintenance decision whose completion includes restoring the intended integrity guarantees.

Related reading on this blog: What is is_not_trusted in sys.foreign_keys? and Foreign Keys Without Indexes: Finding and Fixing Slow Deletes.

Re-enabling a constraint is not validating it, it is turning on checks for new rows only.

Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.