Check for Database Constraints

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.

Leave a Reply

Your email address will not be published. Required fields are marked *