Untrusted Foreign Keys

What this check looks for

Foreign keys across every database where is_not_trusted is 1 in sys.foreign_keys. Keys created NOT FOR REPLICATION are deliberately excluded, because they are permanently untrusted by design and no script will change that.

Why it matters

An untrusted foreign key is still enforced on every write, so nothing fails and nothing waits. What it has stopped being is evidence.

That distinction is the whole of this finding. The constraint still does its job at write time: you cannot insert an orphan row. What it no longer does is tell the query optimizer anything, because SQL Server only trusts a constraint it has verified across every existing row. A key that was added or re-enabled without that verification may have violations already in the table, so the optimizer treats its guarantee as unproven and reasons without it.

The most valuable thing that guarantee buys is join elimination.

Consider a wide view over a normalized schema, joining an order line to its order, its customer, its product and its category. A query selects two columns from the order line and one from the product. With trusted foreign keys, the optimizer knows that:

• Every order line has exactly one matching order, so joining to Orders cannot change the row count.
• No column from Orders is selected.
• Therefore the join to Orders can be removed entirely.

It does the same for every unnecessary parent in the chain, and on a wide reporting view that is routinely the single most valuable simplification the optimizer performs. A view with eight joins can execute as a two table query.

Untrusted, none of that happens. The optimizer cannot prove the relationship holds, so every join is executed, every parent table is read, and the plan is as wide as the view definition.

What untrusts a foreign key:

• Re-enabling it WITH NOCHECK, which is the classic case. A constraint disabled for a data load and then re-enabled with NOCHECK is enforced going forward and untrusted forever.
• A bulk load without CHECK_CONSTRAINTS. BULK INSERT and bcp skip constraint checking by default, which untrusts every constraint on the target table.
• Most restore and merge operations.
• Adding a constraint WITH NOCHECK to avoid the initial scan on a large table.

The common thread is a data loading workflow, which is why this finding tends to appear on data warehouse and staging databases, and why it recurs after every load unless the load itself is fixed.

How to confirm it yourself

Every untrusted foreign key, excluding the ones that are untrusted by design:

SELECT DB_NAME()                          AS [database_name],
       OBJECT_SCHEMA_NAME(fk.[parent_object_id]) AS [schema_name],
       OBJECT_NAME(fk.[parent_object_id])        AS [table_name],
       fk.[name]                          AS [foreign_key],
       OBJECT_NAME(fk.[referenced_object_id])    AS [references_table],
       fk.[is_disabled],
       fk.[is_not_trusted]
  FROM sys.foreign_keys AS fk WITH (NOLOCK)
 WHERE fk.[is_not_trusted] = 1
   AND fk.[is_not_for_replication] = 0
 ORDER BY [table_name], fk.[name];

Across every database:

EXEC sp_MSforeachdb N'
USE [?];
IF DB_ID() > 4
SELECT DB_NAME() AS [database_name],
       OBJECT_SCHEMA_NAME(fk.[parent_object_id]) AS [schema_name],
       OBJECT_NAME(fk.[parent_object_id])        AS [table_name],
       fk.[name]                                 AS [foreign_key],
       fk.[is_disabled]
  FROM sys.foreign_keys AS fk WITH (NOLOCK)
 WHERE fk.[is_not_trusted] = 1 AND fk.[is_not_for_replication] = 0;';

How expensive the re-check will be, which is what you need to sequence the work:

SELECT OBJECT_SCHEMA_NAME(fk.[parent_object_id]) AS [schema_name],
       OBJECT_NAME(fk.[parent_object_id])        AS [table_name],
       fk.[name]                                 AS [foreign_key],
       SUM(p.[rows])                             AS [table_rows],
       CAST(SUM(a.[total_pages]) * 8.0 / 1024 AS DECIMAL(12,1)) AS [table_mb]
  FROM sys.foreign_keys        AS fk WITH (NOLOCK)
 INNER JOIN sys.partitions     AS p  WITH (NOLOCK) ON p.[object_id] = fk.[parent_object_id]
                                                  AND p.[index_id] IN (0, 1)
 INNER JOIN sys.allocation_units AS a WITH (NOLOCK) ON a.[container_id] = p.[partition_id]
 WHERE fk.[is_not_trusted] = 1 AND fk.[is_not_for_replication] = 0
 GROUP BY fk.[parent_object_id], fk.[name]
 ORDER BY [table_rows];

Sorting ascending is deliberate. Do the cheap ones first, so the small tables are all trusted before anything takes a long lock.

Check for existing violations before you try, because a re-check that fails tells you the data has a real problem:

-- orphan rows that would fail the re-check
SELECT COUNT(*) AS [orphans]
  FROM [dbo].[OrderLines] AS c
  LEFT JOIN [dbo].[Orders] AS p ON p.[OrderID] = c.[OrderID]
 WHERE c.[OrderID] IS NOT NULL AND p.[OrderID] IS NULL;

And see the benefit for yourself, which is the most convincing demonstration available. Take a wide view, run a query selecting from only a couple of the tables, and look at the plan before and after re-checking. The eliminated joins simply disappear from it.

How to fix it

One ALTER TABLE ... WITH CHECK CHECK CONSTRAINT per key. Cheap tables first, worst table last.

ALTER TABLE [dbo].[OrderLines]
  WITH CHECK CHECK CONSTRAINT [FK_OrderLines_Orders];

The doubled CHECK CHECK is not a typo. The first is WITH CHECK, meaning verify the existing data; the second is CHECK CONSTRAINT, meaning enable it. WITH NOCHECK CHECK CONSTRAINT enables it without verifying, which is what produced the finding.

Generate the statements in the right order:

SELECT 'ALTER TABLE ' + QUOTENAME(OBJECT_SCHEMA_NAME(fk.[parent_object_id])) + '.'
       + QUOTENAME(OBJECT_NAME(fk.[parent_object_id]))
       + ' WITH CHECK CHECK CONSTRAINT ' + QUOTENAME(fk.[name]) + ';' AS [statement],
       SUM(p.[rows]) AS [table_rows]
  FROM sys.foreign_keys    AS fk WITH (NOLOCK)
 INNER JOIN sys.partitions AS p  WITH (NOLOCK) ON p.[object_id] = fk.[parent_object_id]
                                              AND p.[index_id] IN (0, 1)
 WHERE fk.[is_not_trusted] = 1 AND fk.[is_not_for_replication] = 0
 GROUP BY fk.[parent_object_id], fk.[name]
 ORDER BY [table_rows];

The re-check scans the table under a schema modification lock, so it blocks everything on that table for the duration. On a small table that is milliseconds. On a hundred million row table it is a maintenance window item, which is why the largest one goes last: the cheap wins are all banked before anything blocks for long.

If a re-check fails, the data genuinely violates the constraint. That is a more valuable finding than the performance one, and it needs the orphan rows dealt with before the key can be trusted:

-- find them
SELECT c.*
  FROM [dbo].[OrderLines] AS c
  LEFT JOIN [dbo].[Orders] AS p ON p.[OrderID] = c.[OrderID]
 WHERE c.[OrderID] IS NOT NULL AND p.[OrderID] IS NULL;

Then fix what untrusted them, which is the part that stops this recurring:

• In a bulk load, use CHECK_CONSTRAINTS:

BULK INSERT [dbo].[OrderLines]
  FROM 'D:\Loads\orderlines.csv'
  WITH (CHECK_CONSTRAINTS, TABLOCK, FORMAT = 'CSV');

• In bcp, use the -h "CHECK_CONSTRAINTS" hint.
• In a load that disables constraints, re-enable them WITH CHECK, not WITH NOCHECK, and put that in a TRY ... CATCH so it happens even when the load fails.

A scheduled load is worth fixing once rather than re-checking every week, which is the whole point: without the load fix, this finding returns on the next run.

How long it takes

About two hours for a typical database, and the estimate is dominated by the largest table’s re-check rather than by the number of keys.

Related reports

Related checks

Frequently asked questions

Is my data at risk with an untrusted foreign key? No. It is still enforced on every insert and update. What it no longer does is inform the query optimizer, which is a performance cost rather than an integrity one.

What does join elimination actually save? On a wide view over a normalized schema, it removes every parent table join where no column from that table is selected. A view with eight joins can execute as a two table query.

Why does the re-check take so long? It scans the whole table to verify every existing row, under a schema modification lock. That is why the work is sequenced cheapest first, with the largest table left for a window.

They went untrusted again after the nightly load. The load is omitting CHECK_CONSTRAINTS. Fixing the load is the durable answer; re-checking every week is not.