Skipped load validation can leave rows that violate the declared schema. DBCC CHECKCONSTRAINTS can expose those violations before the table returns to normal use. Fix the rows deliberately, then re-enable the constraints with validation so SQL Server can trust their guarantees again.
Separate Disabled Enforcement From Lost Trust
A constraint can be disabled so new modifications bypass its enforcement. It can also be enabled but untrusted because existing rows were not validated. Those are separate metadata states. Re-enabling the rule alone does not necessarily prove the loaded rows satisfy it.
I inspect both states after a load that bypassed checks. A successful INSERT says the load statement completed under the chosen rules. It does not establish that a disabled foreign key or CHECK would have accepted every row.
The demonstration uses a disposable database and intentionally invalid synthetic rows. Do not disable production integrity rules merely to make the load faster without an approved validation and recovery plan. The bypass needs a clear scope and end condition. Opening the inspection gate saves time only if somebody still inspects what passed through it.
Create a Controlled Constraint Violation
The next setup creates a parent table and a child table with both a foreign key and an amount CHECK. A valid row goes in first. NOCHECK then disables the child's constraints, and the deliberately invalid row refers to a missing parent and contains an invalid amount.
Use unused names in a disposable database. The purpose is to make the later inspection meaningful without damaging a shared table. Keep the original rules visible in the CREATE statement so the expected violations are easy to explain.
I retain an identifier for every invalid test row. That makes the repair precise. In a real load, keep the staging source and rejection evidence as well. An invalid amount and a missing parent need different business responses; neither should automatically become a deleted row just because deletion makes the validation query return less output. The correct repair comes from the intended data contract.
CREATE TABLE dbo.LoadParentDemo(ParentID int NOT NULL PRIMARY KEY);
INSERT dbo.LoadParentDemo VALUES(1);
CREATE TABLE dbo.LoadChildDemo
(ChildID int NOT NULL PRIMARY KEY,ParentID int NOT NULL,Amount int NOT NULL,
CONSTRAINT FK_LoadChildDemo_Parent FOREIGN KEY(ParentID) REFERENCES dbo.LoadParentDemo(ParentID),
CONSTRAINT CK_LoadChildDemo_Amount CHECK(Amount>=0));
INSERT dbo.LoadChildDemo VALUES(1,1,10);
ALTER TABLE dbo.LoadChildDemo NOCHECK CONSTRAINT ALL;
INSERT dbo.LoadChildDemo VALUES(2,999,-1);Inspect Disabled Rules With DBCC CHECKCONSTRAINTS
DBCC CHECKCONSTRAINTS can inspect a selected table or the relevant constraints across the database. ALL_CONSTRAINTS includes disabled constraints, which is essential in this example. An ordinary check that skips disabled rules can appear quiet while the bypassed table remains invalid.
ALL_ERRORMSGS requests the fuller error reporting option rather than accepting the default output limit. Read the returned table, constraint, and WHERE information. The WHERE text describes violating data and can guide a focused SELECT for investigation.
What does each returned row actually identify? Inspect the underlying data and the constraint definition. Do not execute returned predicates as an automatic deletion program. Multiple violations on one row can require repeated review as earlier problems are repaired. The output is diagnostic evidence, and DBCC's checks do not replace every possible integrity validation. Keep full CHECKDB responsibilities separate from this constraint-focused inspection.
DBCC CHECKCONSTRAINTS(N'dbo.LoadChildDemo') WITH ALL_CONSTRAINTS,ALL_ERRORMSGS;
DBCC CHECKCONSTRAINTS WITH ALL_CONSTRAINTS,ALL_ERRORMSGS;
Repair Rows, Then Rerun DBCC CHECKCONSTRAINTS
The controlled test row is repaired to the synthetic parent and a permitted amount. That is appropriate only because the sample's intended values are known. A real staging row needs source reconciliation, quarantine, correction, or an approved rejection policy.
The next block performs the narrow demonstration repair and runs the targeted inspection again. Confirm that the identified row now satisfies both rules. If another violation appears, inspect it rather than declaring the process done after one successful update.
Do not manufacture a parent record solely to satisfy a foreign key unless that parent is a valid business entity. Likewise, changing a negative amount to zero is not a general accounting repair. Integrity constraints detect a disagreement; they do not determine the correct replacement value. Keep the original invalid input and the reason for its chosen resolution when the load must be audited.
UPDATE dbo.LoadChildDemo SET ParentID=1,Amount=0 WHERE ChildID=2;
DBCC CHECKCONSTRAINTS(N'dbo.LoadChildDemo') WITH ALL_CONSTRAINTS,ALL_ERRORMSGS;Restore Enabled and Trusted Constraints
WITH CHECK CHECK CONSTRAINT performs the existing-row validation while enabling the constraints. The repeated CHECK words express different parts of the command: validate the data, then enable the constraint action. Plain CHECK CONSTRAINT can enable without establishing the same trusted state for bypassed data.
The next block uses the validated enable operation and reads is_disabled and is_not_trusted from both foreign-key and CHECK metadata. A successful command followed by the intended flags is the configuration readback. Keep the violation checks and business reconciliation as separate supporting evidence.
Trusted constraints can also help the optimizer reason about valid relationships. Leaving them untrusted affects more than whether a future row is rejected. Do not stop at a green enabled checkbox in a dialog. Read the actual flags and confirm the intended rule definitions still exist. The load should return the table to its approved integrity contract, not merely make future inserts encounter an error again.
ALTER TABLE dbo.LoadChildDemo WITH CHECK CHECK CONSTRAINT ALL;
SELECT name,is_disabled,is_not_trusted FROM sys.foreign_keys
WHERE parent_object_id=OBJECT_ID(N'dbo.LoadChildDemo')
UNION ALL
SELECT name,is_disabled,is_not_trusted FROM sys.check_constraints
WHERE parent_object_id=OBJECT_ID(N'dbo.LoadChildDemo');Plan the Validation Cost Before the Load
Constraint checking scans relevant data and can be expensive on a large table. Schedule the full inspection and trusted re-enable during the approved workload window. A fast bypass followed by an unplanned long validation is not a complete loading strategy.
Test the invalid-input and repair paths before production use. Keep the staging evidence until validation and application checks succeed. If the trusted re-enable fails, leave the status explicit and resolve the remaining violations rather than calling the load complete.
DBCC CHECKCONSTRAINTS is useful when it closes a deliberate validation gap with inspectable evidence. Include disabled rules, repair rows according to their meaning, and read back both enabled and trusted state. The table is ready when its data and its integrity guarantees agree again.
Related reading on this blog: What is is_not_trusted in sys.foreign_keys? and Loading Large Files Fast With BULK INSERT.
An enabled constraint is not always a trusted constraint, it is a rule whose old rows still need validation.
Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.
