Foreign Key Trust and Indexing: the detection query for untrusted and unindexed foreign keys

Revision history: added the not-for-replication material on July 10, 2018, then expanded the table seek/scan stats and fix generation on September 9, 2018. On August 15, 2026, I corrected reversed referencing and target column labels in the embedded query and narrowed two performance claims that were too absolute.

Archive note: originally published June 25, 2018 and retained with the corrections recorded above. Untrusted and unindexed foreign keys remain worth checking on current versions. See also Eleven SQL Server Checks, Ordered by Return on the Hour Spent.

Foreign keys are ubiquitous in the relational database world. They define how two tables and the data within are related. This is critical information, both for the consumer of the data and for SQL Server to determine the appropriate means of joining and serving the data.

As a consumer, we understand the relationship between two tables by the columns joined and we make certain assumptions because of its existence. SQL Server does the same thing, but the consequences of not being able to trust the relationship can be devastating on performance.

What does this mean?

If referential integrity of the relationship has been lost or removed, a foreign key can no longer be considered trustworthy. It remains intact for logical reasons and can still be enforced for future inserts and updates, but it will remain in an untrusted and degraded state until corrected.

When a foreign key cannot be trusted, the optimizer cannot use the relationship as proof when simplifying joins or estimating work. That can produce more expensive plans as table sizes increase, although the effect depends on the query and workload. When a foreign key column has no useful index, common joins and referential checks may require costly scans; confirm the benefit against the actual workload and execution plan before adding one.

How does it happen?

This occurs most often when loading data. When you’re dealing with large amounts of data, it’s common to disable the foreign keys to improve performance. It’s also unfortunately common that when the load is complete, the foreign key is re-enabled but without re-checking the data to ensure relationship trust. Once this happens, the foreign key is untrusted.

Another way this can happen is being disabled during troubleshooting. Under duress it’s tempting to abandon some safeguards to get a production system back online quickly.

This can also occur when bulk inserts are used without specifying the CHECK_CONSTRAINTS hint.

Finally, if you create a foreign key with NOT_FOR_REPLICATION then it won’t be enforced when the replication agent adds, updates, or deletes data, so it can’t be trusted.

How do I find untrusted and unindexed foreign keys?

Using the following SQL snippet, you can see which foreign keys are untrusted or unindexed:

-- foreign key index and trust status (current database)

declare @Table varchar(64) = '' ;	-- leave blank or null to see fk status for all tables
declare @ShowFixes bit = 1 ;		-- also create output of just fixes
declare @ShowTrusted bit = 1 ;		-- show trusted foreign keys
declare @ShowIndexed bit = 1 ;		-- show indexed foreign keys
declare @ShowNFR bit = 1 ;			-- show foreign keys identified NOT_FOR_REPLICATION

set nocount on ;
set transaction isolation level read uncommitted ;
declare @crlf char(2) = char(13) + char(10) ;
if object_id('tempdb..#fks') is not null drop table [#fks] ;

with [cte] as (
    select
        [schema_name] = s.[name],
        [table_name] = t.[name],
        ps.[row_count],
        [used_pages_count] = Sum(ps.[used_page_count]),
        [pages] = Sum(case when (i.[index_id] < 2) then (ps.[in_row_data_page_count] + ps.[lob_used_page_count] + ps.[row_overflow_used_page_count]) else ps.[lob_used_page_count] + ps.[row_overflow_used_page_count] end),
        [table_activity] = Coalesce(Sum(us.[user_seeks] + us.[user_scans]), 0)
    from
        sys.dm_db_partition_stats [ps]
        inner join sys.tables [t] on ps.[object_id] = t.[object_id]
        inner join sys.indexes [i] on t.[object_id] = i.[object_id] and ps.[index_id] = i.[index_id]
        inner join sys.schemas [s] on t.[schema_id] = s.[schema_id]
        left outer join sys.dm_db_index_usage_stats [us] on i.[object_id] = us.[object_id] and i.[index_id] = us.[index_id] and us.database_id = db_id()
    where
        t.[is_ms_shipped] = 0
    group by
        s.[name],
        t.[name],
        ps.[row_count]
)
select distinct
    [fk_name] = fk.[name],
    [referencing_schema] = s1.[name],
    [referencing_table] = object_name(fk.[parent_object_id]),
    [referencing_column] = c.[name],
    [referencing_row_count] = ts1.[row_count],
    [referencing_table_activity] = ts1.[table_activity],
    [referencing_table_size_mb] = Convert(decimal(18,2), (ts1.[pages] * 8) / 1024.0),
    [referencing_index_size_mb] = Convert(decimal(18,2), (case when ts1.[used_pages_count] > ts1.[pages] then ts1.[used_pages_count] - ts1.[pages] else 0 end * 8) / 1024.0),
    [target_schema] = s2.[name],
    [target_table] = object_name(fk.[referenced_object_id]),
    [target_column] = c2.[name],
    [target_row_count] = ts2.[row_count],
    [target_table_activity] = ts2.[table_activity],
    [target_table_size_mb] = Convert(decimal(18,2), (ts2.[pages] * 8) / 1024.0),
    [target_index_size_mb] = Convert(decimal(18,2), (case when ts2.[used_pages_count] > ts2.[pages] then ts2.[used_pages_count] - ts2.[pages] else 0 end * 8) / 1024.0),
    fk.[is_disabled],
    fk.[is_not_trusted],
    fk.[is_not_for_replication],
    [is_not_indexed] = case when ic.[object_id] is null then 1 else 0 end,
    [sqlx_idx] = case when ic.[object_id] is null then 'raiserror('':: creating [IX_' + object_name(fk.[parent_object_id]) + '_' + c.[name] + ']'', 10, 1) with nowait ;' + @crlf + 'create nonclustered index [IX_' + object_name(fk.[parent_object_id]) + '_' + c.[name] + '] on ' + s1.[name] + '.' + QuoteName(object_name(fk.[parent_object_id])) + '(' + QuoteName(c.[name]) + ' asc)' + case when ServerProperty('EngineEdition') = 3 then ' with (data_compression = page)' else '' end + ' ;' else null end,
    [sqlx_chk] = case when fk.[is_not_trusted] = 1 and fk.[is_not_for_replication] = 0 then 'raiserror('':: checking ' + QuoteName(fk.[name]) + ''', 10, 1) with nowait ;' + @crlf + 'begin try' + @crlf + '	alter table ' + s1.[name] + '.' + QuoteName(object_name(fk.[parent_object_id])) + ' with check check constraint ' + QuoteName(fk.[name]) + ' ;' + @crlf + 'end try' + @crlf + 'begin catch' + @crlf + '	raiserror('' - ORPHAN(S) DETECTED:'', 10, 1) with nowait ;' + @crlf + '	dbcc checkconstraints (''' + s1.[name] + '.' + fk.[name] + ''') ;' + @crlf + 'end catch' else null end,
    [sqlx_dbcc] = case when fk.[is_not_trusted] = 1 and fk.[is_not_for_replication] = 0 then 'dbcc checkconstraints (''' + s1.[name] + '.' + fk.[name] + ''') ;' else null end
into
    [#fks]
from
    sys.foreign_keys [fk]
    inner join sys.schemas [s1] on fk.[schema_id] = s1.[schema_id]
    inner join sys.objects [o] on fk.[referenced_object_id] = o.[object_id]
    inner join sys.schemas [s2] on o.[schema_id] = s2.[schema_id]
    inner join sys.foreign_key_columns [fkc] on fkc.[constraint_object_id] = fk.[object_id]
    inner join sys.columns [c] on fkc.[parent_object_id] = c.[object_id] and fkc.[parent_column_id] = c.[column_id]
    inner join sys.columns [c2] on fkc.[referenced_object_id] = c2.[object_id] and fkc.[referenced_column_id] = c2.[column_id]
    left outer join sys.index_columns [ic] on ic.[object_id] = fkc.[parent_object_id] and ic.[column_id] = fkc.[parent_column_id] and ic.[index_column_id] = fkc.[constraint_column_id]
    inner join [cte] [ts1] on s1.[name] = ts1.[schema_name] and object_name(fk.[parent_object_id]) = ts1.[table_name]
    inner join [cte] [ts2] on s2.[name] = ts2.[schema_name] and object_name(fk.[referenced_object_id]) = ts2.[table_name]
where
    (
        @Table is null
        or @Table = ''
        or fk.[referenced_object_id] = (select [object_id] from sys.tables where [name] = @Table)
    )
    and	(@ShowTrusted = 1 or fk.[is_not_trusted] = 1)
    and (@ShowIndexed = 1 or ic.[object_id] is null)
    and (@ShowNFR = 1 or fk.[is_not_for_replication] = 0)
order by
    s1.[name] asc,
    [referencing_table] asc,
    fk.[name] asc ;

select * from [#fks] ;

if (@ShowFixes = 1)
    select distinct [sqlx] = [sqlx_chk] + @crlf + 'go', [dbcc] = [sqlx_dbcc] + @crlf + 'go' from [#fks] where [sqlx_chk] is not null union
    select distinct [sqlx] = [sqlx_idx] + @crlf + 'go', [dbcc] = null from [#fks] where [sqlx_idx] is not null ;
go

Foreign key index and trust status SQL source

How do I fix it?

To correct this issue, the foreign key relationships must be re-checked using the following statement (adjusted to suit):

alter table [schema].[table] with check check constraint [keyname] ;

If you receive an error due to a foreign key constraint, use the DBCC CHECKCONSTRAINTS command to see where the integrity issue lies:

dbcc checkconstraints('schema.table') ;

The above detection query also provides ready-to-run snippets for correcting the issues if found.