Untrusted Constraints

Overview

A foreign key that is not trusted is not a weaker constraint. It is an absent one, as far as the query optimizer is concerned.

It still enforces on every write, so nothing fails, nothing waits, and nothing turns up in a wait statistic. What it has stopped being is evidence, and evidence was the only thing the optimizer ever used it for. Join elimination stops. Predicate elimination stops. The plans are simply worse than they should be, across the whole database, permanently, and there is no symptom to notice.

This is a whole class of plan quality loss with no error message attached to it.

The Untrusted Constraints report lists every foreign key and check constraint in a database with a verdict that names the specific optimization that is not happening, rather than repeating the flag back at you.

Why it is not just a flag

sys.foreign_keys.is_not_trusted is a bit, and every tool in this space reports the bit. A bit tells you a state; it does not tell you a consequence. The consequence is the whole point:

• An untrusted foreign key means the optimizer cannot remove a join to the parent table even when no column from that table is selected. On a wide view over a normalized schema, that is the single most valuable simplification it performs, and it is switched off.
• An untrusted check constraint means no contradiction detection. A trusted check on a date range lets the optimizer notice that a query’s WHERE clause conflicts with the constraint and skip the table without reading it. That is what makes archive tables and partitioned views cheap. Untrusted, every branch of the union gets read.

Where to find it

This is a database-level report. Select a database in the tree, then open Untrusted Constraints.

It needs SQL Server 2008 or newer. Everything on the page is 2005 or older except sys.indexes.has_filter, which is what lets the report tell an index that can genuinely support a foreign key check from a filtered one that only covers part of the table.

The page title reads Untrusted Constraints for <database name>.

The six verdicts

Every constraint lands in exactly one band. The bands are ordered worst first, and the grid sorts by them.

Enforcing only is a write cost, not a plan cost

Enforcing only is the odd one out and is deliberately kept separate. Nothing is wrong with the constraint: it is trusted and the optimizer can use it. The cost is on the other side. When no index on the child table has the foreign key columns as its leading columns, every delete and every key update on the parent scans the child to prove the row is unreferenced.

That work does appear in the execution plan of the delete, usually as access to the child table under an Assert. What it is absent from is the statement text. Nothing in

DELETE FROM Customer WHERE CustomerId = 42;

mentions the eleven child tables it is about to probe, which is why the cost is so often attributed to a statement that appears to touch nothing.

The report reads key_ordinal, not merely “do these columns appear in an index”. A key whose columns sit third and fourth in a composite index is not supported, and a filtered index does not count either, because the constraint check needs every row.

The Nullable column

This is the detail that separates a report that is right from one that merely sounds right.

Restoring trust on a foreign key does not by itself restore inner join elimination.

• For an inner join from child to parent, the optimizer may only remove the parent when the key is trusted and the child column is NOT NULL. With a nullable column the join is also acting as a filter, and removing it would change the result.
• For a left outer join, trust alone is enough.

So the grid carries a Nullable column, and the verdict for a nullable key says left join elimination only rather than promising a simplification that never arrives.

The Cascades view

The second view draws the delete cascade paths as a node-link diagram. ON DELETE CASCADE is written once, when the table is created, and then never looked at again. A three-level cascade is a lock footprint nobody has measured.

Each row is one root-to-leaf path. The filled node is the table the delete names; every node after it is a table SQL Server visits on its own initiative. The label above each arrow is the referential action that link carries, because CASCADE, SET NULL and SET DEFAULT are three different amounts of work. The amber number on a node’s corner is its fan out: how many child tables it cascades into. A path three deep and one wide is a chain; a path three deep and forty wide at the second level is an incident.

The view does not claim a row count for the blast radius. Working out how many rows a delete would actually remove needs the data, not the catalog, so the right-click menu gives you a SELECT to run instead of a number invented from table sizes.

The cost of fixing it

The fix is one statement per constraint:

ALTER TABLE [dbo].[OrderLine] WITH CHECK CHECK CONSTRAINT [FK_OrderLine_Order];

The page does not present that as free, because it is not:

• It takes a schema modification lock on the table and holds it for the whole validation scan. Nothing else reads or writes that table while it runs. This is a maintenance window on anything large, not a quick fix.
• If any existing row violates the constraint, the statement fails with error 547 and the constraint stays untrusted. That is most likely in the Inert band, where writes have not been checked for however long the constraint has been off.

So every generated script states the row count, the page count, and a duration band, with the assumption behind the estimate printed next to it so you can divide by it if your storage is faster. An estimate whose basis is hidden is a guess wearing a number.

Nothing on this page runs anything. The right-click menu scripts the statements to the clipboard, and double-clicking a row opens them in a read-only window that has no execute button at all.

Where the data comes from

Row and page counts come from sys.partitions and sys.allocation_units rather than from sys.dm_db_partition_stats, so the numbers behind the re-check estimate do not need VIEW DATABASE STATE. The write volume column does need it, and is the only thing that disappears on a locked-down login.

The write volume column lies in one specific way

sys.dm_db_index_operational_stats counters are not cumulative since the instance started. A row appears when an object’s metadata enters the cache and disappears when it is evicted or the index is rebuilt, so two tables on the same instance can have completely different observation windows. The report says in at most the N days since rather than since, because the true window can be shorter and nothing records that it was.

A zero means “nothing has been counted”, never “this never happens”.

How constraints become untrusted

• Re-enabling with WITH NOCHECK, or NOCHECK CONSTRAINT followed by CHECK CONSTRAINT, which switches enforcement back on without asking SQL Server to prove the existing data still fits.
• A bulk load that omits CHECK_CONSTRAINTS. This is the common one, and it is why archive tables so often carry an untrusted date check.
• Most restore and merge operations.

If a scheduled load untrusts the same constraints every week, re-checking them every week is the wrong fix. Fix the load.

Quick Scan

Two Quick Scan findings point here, both reported as one row per database with a count rather than one row per constraint:

• Untrusted Foreign Keys
• Untrusted Check Constraints

Both exclude NOT FOR REPLICATION constraints, because those are permanently untrusted by design and counting them would pad a number that is supposed to be actionable.

Related reports

Frequently asked questions

Nothing is broken. Why does this matter? Because nothing being broken is the problem. There is no error, no wait, and no failure. The plans are just worse than they should be, everywhere, until somebody looks.

Why is a NOT FOR REPLICATION key shown if I cannot fix it? So the count on the page reconciles with the catalog. It is kept out of the fixable total and gets no script, because offering a statement that provably cannot work is worse than offering nothing.

Why does my key still not eliminate the join after I trusted it? Check the Nullable column. An inner join to the parent can only be removed when the child column is NOT NULL. If it is nullable, you get outer join elimination and nothing more.

Why does the report say my foreign key has no index when the column is in one? Because it is not the leading column, or the index is filtered. Either way the constraint check cannot use it.

Can I run the fix from the report? No. Re-checking takes a schema modification lock for the length of a full table scan. You get the script, with the row count and an estimate.

The re-check failed with error 547. Is that a bug? No, that is the finding. The data already violates the constraint, which is what a disabled constraint allows. Fix the rows, then re-check.