Untrusted foreign keys and CHECK constraints

In one line: a constraint re-enabled with CHECK CONSTRAINT still blocks bad rows, but SQL Server no longer trusts it, so the optimizer can’t use it to remove joins or skip impossible filters. Only WITH CHECK CHECK CONSTRAINT makes it trusted again.

Run it yourself: the scripts that reproduce everything on this page are in sqlserver-demos/untrusted-constraints on GitHub.

What you’ll see

  • A join to a table the query takes no columns from, still in the plan. In the demo, the total order value read 4,205 pages instead of 1,370.
  • A filter the CHECK constraint makes impossible (Status = 7 when status must be 0 to 3) scanning the whole table: 1,370 reads instead of 6.
  • is_not_trusted = 1 in sys.foreign_keys or sys.check_constraints, with is_disabled = 0.
  • Error 547 when you try to make the constraint trusted again.

Why it happens

The optimizer only relies on a constraint that SQL Server has checked against every row in the table. With a trusted foreign key, every order has exactly one customer, so a join to customers that uses none of their columns can be removed. With a trusted CHECK constraint, a filter outside its range can’t match, and the table isn’t read.

ALTER TABLE ... NOCHECK CONSTRAINT followed by CHECK CONSTRAINT enables the constraint again without checking the rows that arrived while it was off, so it is no longer trusted. A constraint added WITH NOCHECK starts out untrusted. A NOT FOR REPLICATION constraint is never trusted, even after checking every row.

An untrusted key isn’t being cautious for nothing: in the demo, an order for a customer that doesn’t exist got in while the key was disabled, and the join the optimizer kept is what left it out of the total.

What to do

  • Find them: run the query below in each database.
  • Find the rows that break each one with a NOT EXISTS query against the parent table, and fix or delete them.
  • Re-trust it with ALTER TABLE ... WITH CHECK CHECK CONSTRAINT .... It checks every row and holds a schema modification lock (Sch-M) on both tables until it finishes, so on a large table, do it in a maintenance window.
  • Drop and re-add NOT FOR REPLICATION constraints without that option if you don’t use replication.
  • After every bulk load, check is_not_trusted. BULK INSERT and bcp don’t check constraints unless asked to.

What doesn’t help

  • ALTER TABLE ... CHECK CONSTRAINT on its own. It enables the constraint and leaves it untrusted.
  • Updating statistics or rebuilding indexes. Trust comes only from checking the rows.

Check your server

-- Untrusted foreign keys and CHECK constraints in the current database,
-- and the statement that re-trusts each one.
SELECT OBJECT_SCHEMA_NAME(k.parent_object_id) + '.' + OBJECT_NAME(k.parent_object_id) AS table_name,
       k.name AS constraint_name, k.type_desc, k.is_disabled, k.is_not_for_replication,
       'ALTER TABLE ' + QUOTENAME(OBJECT_SCHEMA_NAME(k.parent_object_id)) + '.'
       + QUOTENAME(OBJECT_NAME(k.parent_object_id))
       + ' WITH CHECK CHECK CONSTRAINT ' + QUOTENAME(k.name) + ';' AS fix
FROM (SELECT name, parent_object_id, type_desc, is_disabled, is_not_trusted, is_not_for_replication
      FROM sys.foreign_keys
      UNION ALL
      SELECT name, parent_object_id, type_desc, is_disabled, is_not_trusted, is_not_for_replication
      FROM sys.check_constraints) AS k
WHERE k.is_not_trusted = 1
  AND OBJECTPROPERTY(k.parent_object_id, 'IsMSShipped') = 0
ORDER BY table_name, constraint_name;

See it happen

The untrusted-constraints demo measures the same two queries with trusted and untrusted constraints, and shows:

  • the join removed, and back
  • WITH NOCHECK and NOT FOR REPLICATION
  • re-trusting failing with error 547, then succeeding
  • the Sch-M locks re-trusting takes

Related: missing-index suggestions · minimal logging · lock escalation

Measured on SQL Server 2025 RTM-CU10 (17.0.5035.5) and SQL Server 2022 RTM-CU25 (16.0.4255.1), Linux containers. Your numbers will differ; the behaviour shouldn’t.

Part of SQL Server Demos: short explanations of SQL Server problems, each backed by a runnable demo in MarlonRibunal/sqlserver-demos on GitHub. Written with the help of AI; every number comes from running the demo on SQL Server. How this was made.

Verified by MonsterInsights