Constraints are your data-integrity contract, but the contract only holds if it’s actually being enforced. A CHECK or FOREIGN KEY constraint that was added WITH NOCHECK, disabled for a bulk load and never re-checked, or created before legacy data arrived can leave invalid rows sitting in a table indefinitely — the constraint exists, the catalog says everything is fine, but the data violates it. This post is about finding those rows.
The tool: DBCC CHECKCONSTRAINTS
The one-command answer is DBCC CHECKCONSTRAINTS. It builds and runs, for every CHECK and FOREIGN KEY constraint you point it at, a query that returns exactly the rows that violate the constraint:
-- Check one table (all its enabled constraints)
DBCC CHECKCONSTRAINTS ('dbo.Orders');
-- Check one specific constraint
DBCC CHECKCONSTRAINTS ('dbo.CK_Orders_OrderDate');
-- Check every enabled constraint in the whole database
DBCC CHECKCONSTRAINTS;
-- Include disabled and NOT-FOR-REPLICATION constraints too
DBCC CHECKCONSTRAINTS WITH ALL_CONSTRAINTS;
-- Report every violating row, not just the first 200 per check
DBCC CHECKCONSTRAINTS WITH ALL_ERRORMSGS;
-- Quiet output: results only, no row-count messages
DBCC CHECKCONSTRAINTS WITH NO_INFOMSGS;
Two details from the documentation that matter in practice:
- By default only enabled constraints are checked. A constraint that someone disabled and never re-enabled is invisible unless you pass
ALL_CONSTRAINTS— which is precisely the situation you’re usually hunting for, so make it a habit. - Output is capped at the first 200 violating rows per check unless you add
ALL_ERRORMSGS. If a bulk load went wrong, 200 rows may be the tip of the iceberg.
Finding the landmines before they detonate
For a database-wide audit, the untrusted constraints are easier to list first — these are constraints the optimizer no longer trusts because existing data was never validated against them:
SELECT fk.[name] AS constraint_name,
t.[name] AS table_name,
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_not_trusted = 1 OR fk.is_disabled = 1;
SELECT cc.[name] AS constraint_name,
t.[name] AS table_name,
cc.is_disabled,
cc.is_not_trusted
FROM sys.check_constraints AS cc
JOIN sys.tables AS t ON t.object_id = cc.parent_object_id
WHERE cc.is_not_trusted = 1 OR cc.is_disabled = 1;
Then point DBCC CHECKCONSTRAINTS at the offenders (or just run it with ALL_CONSTRAINTS) to get the actual bad rows, and repair or delete them before re-enabling with WITH CHECK CHECK CONSTRAINT — the only form of re-enable that revalidates the data and flips is_not_trusted back to 0.
Related: the query in SQL Script for Constraints lists every foreign key with its columns and referenced table — handy for mapping what CHECKCONSTRAINTS reports back to your schema. And if you’re about to mass-delete data, disable nothing: read Empty your Database & Reset Identity Columns first for the constraint-handling order.