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.

September 10, 2026: Corrected the index test to check the complete foreign key against one index, generated composite index candidates, quoted object names, and corrected table totals and schema filtering. The original test could mistake included columns or columns in separate indexes for a supporting index.

September 23, 2026: Included disabled constraints in the diagnostic checks and clarified when to use them.

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.

A foreign key describes a relationship SQL Server can use when planning a query. If that relationship is no longer trusted, the optimizer loses that evidence. Missing indexes can make the joins and integrity checks more expensive too.

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:

Here, indexed means that all referencing columns form the leading keys of one enabled, unfiltered clustered or nonclustered rowstore index. Their order may differ from the foreign key, and the index may have more keys after them. Included columns do not count. This is a conservative inventory: filtered and other index types need separate workload review. The query returns one row per foreign-key column, with the same index verdict and candidate statement for the whole key.

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

declare @Table sysname = null ;	-- referenced table name; null or blank means all
declare @Schema sysname = null ;	-- referenced table schema; null or blank means all
declare @ShowFixes bit = 1 ;		-- also create output of candidate statements
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 quoted_identifier on ;
declare @crlf char(2) = char(13) + char(10) ;
if object_id('tempdb..#fks') is not null drop table [#fks] ;

with [table_pages] as (
    select
        ps.[object_id],
        [row_count] = Sum(case when ps.[index_id] < 2 then ps.[row_count] else 0 end),
        [used_pages_count] = Sum(ps.[used_page_count]),
        [pages] = Sum(case when ps.[index_id] < 2 then ps.[in_row_data_page_count] + ps.[lob_used_page_count] + ps.[row_overflow_used_page_count] else 0 end)
    from
        sys.dm_db_partition_stats [ps]
    group by
        ps.[object_id]
), [table_usage] as (
    select
        us.[object_id],
        [table_activity] = Sum(us.[user_seeks] + us.[user_scans])
    from
        sys.dm_db_index_usage_stats [us]
    where
        us.[database_id] = db_id()
    group by
        us.[object_id]
), [cte] as (
    select
        p.*,
        [table_activity] = Coalesce(u.[table_activity], 0)
    from
        [table_pages] [p]
        inner join sys.tables [t] on p.[object_id] = t.[object_id]
        left outer join [table_usage] [u] on p.[object_id] = u.[object_id]
    where
        t.[is_ms_shipped] = 0
)
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],
    [supporting_index] = ix.[name],
    [is_not_indexed] = case when ix.[index_id] is null then 1 else 0 end,
    [sqlx_idx] = case when ix.[index_id] is null then
        N'create nonclustered index ' + QuoteName(N'IX_FK_' + Convert(nvarchar(12), fk.[object_id]))
        + N' on ' + names.[table_name] + N' (' + cols.[column_list] + N') ;' end,
    [sqlx_chk] = case when fk.[is_not_trusted] = 1 and fk.[is_not_for_replication] = 0 then
        N'alter table ' + names.[table_name] + N' with check check constraint ' + QuoteName(fk.[name]) + N' ;' end,
    [sqlx_dbcc] = case when fk.[is_not_trusted] = 1 and fk.[is_not_for_replication] = 0 then
        N'dbcc checkconstraints (N''' + Replace(names.[table_name], N'''', N'''''') + N''') with all_constraints ;' 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]
    inner join [cte] [ts1] on fk.[parent_object_id] = ts1.[object_id]
    inner join [cte] [ts2] on fk.[referenced_object_id] = ts2.[object_id]
    cross apply (
        select [table_name] = QuoteName(s1.[name]) + N'.' + QuoteName(object_name(fk.[parent_object_id]))
    ) [names]
    cross apply (
        select
            [column_count] = (select Count(*) from sys.foreign_key_columns where [constraint_object_id] = fk.[object_id]),
            [column_list] = Stuff((
                select N', ' + QuoteName(fc.[name])
                from sys.foreign_key_columns [f]
                    inner join sys.columns [fc] on f.[parent_object_id] = fc.[object_id] and f.[parent_column_id] = fc.[column_id]
                where f.[constraint_object_id] = fk.[object_id]
                order by f.[constraint_column_id]
                for xml path(''), type
            ).value('.', 'nvarchar(max)'), 1, 2, N'')
    ) [cols]
    outer apply (
        select top (1) i.[index_id], i.[name]
        from sys.indexes [i]
        where i.[object_id] = fk.[parent_object_id]
            and i.[type] in (1, 2)
            and i.[is_disabled] = 0 and i.[is_hypothetical] = 0 and i.[has_filter] = 0
            and (
                select Count(*)
                from sys.index_columns [ic]
                    inner join sys.foreign_key_columns [f] on f.[parent_object_id] = ic.[object_id] and f.[parent_column_id] = ic.[column_id]
                where ic.[object_id] = i.[object_id] and ic.[index_id] = i.[index_id]
                    and f.[constraint_object_id] = fk.[object_id]
                    and ic.[is_included_column] = 0 and ic.[key_ordinal] between 1 and cols.[column_count]
            ) = cols.[column_count]
        order by i.[index_id]
    ) [ix]
where
    (@Table is null or @Table = '' or o.[name] = @Table)
    and (@Schema is null or @Schema = '' or s2.[name] = @Schema)
    and (@ShowTrusted = 1 or fk.[is_not_trusted] = 1)
    and (@ShowIndexed = 1 or ix.[index_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

Original SQL source, before the September 2026 corrections above

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 validation fails, read the error first. For a foreign-key violation, use DBCC CHECKCONSTRAINTS to locate the integrity issue:

dbcc checkconstraints('schema.table') with all_constraints ;

WITH ALL_CONSTRAINTS includes disabled constraints; without it, a table-level check only checks enabled constraints. DBCC CHECKCONSTRAINTS may not report every violation in one pass, so run it again after repairs.

The detection query generates candidate statements; it does not execute them. An index candidate covers every column of the foreign key, rather than creating separate single-column indexes. Review the workload, index names, storage cost, and constraint state before running any candidate. The generated name uses the foreign-key object ID; check for an existing index with that name. Constraint validation can scan the table and block other work, and re-checking a disabled constraint also re-enables it. Index-usage counters describe the current observation window, not the lifetime workload.