List Foreign Key Relationships in SQL Server: Columns, Rules and Untrusted Constraints

When you inherit an unfamiliar database, plan a data migration or archiving job, or try to understand why rows in a table can’t be deleted, the first thing you need is a map of how the tables relate. Foreign keys keep that map inside the database itself; with the right query, you can see in seconds which table is tied to which, through which columns and under which rules.

This article shows how to list foreign key relationships, find and fix untrusted constraints, detect foreign key columns without an index, and sort tables into dependency order.

Listing All Foreign Key Relationships

The query below returns, for every foreign key in the database, the child and parent table, the columns in order, the delete and update rules and the state of the constraint. Multi-column foreign keys are combined into one row (STRING_AGG ... WITHIN GROUP requires SQL Server 2017 or later):

SELECT fk.name                                              AS constraint_name,
       OBJECT_SCHEMA_NAME(fk.parent_object_id) + N'.' + OBJECT_NAME(fk.parent_object_id)         AS child_table,
       STRING_AGG(pc.name, N', ') WITHIN GROUP (ORDER BY fkc.constraint_column_id)                AS child_columns,
       OBJECT_SCHEMA_NAME(fk.referenced_object_id) + N'.' + OBJECT_NAME(fk.referenced_object_id) AS parent_table,
       STRING_AGG(rc.name, N', ') WITHIN GROUP (ORDER BY fkc.constraint_column_id)                AS parent_columns,
       fk.delete_referential_action_desc AS on_delete,
       fk.update_referential_action_desc AS on_update,
       fk.is_disabled,
       fk.is_not_trusted
FROM sys.foreign_keys AS fk
JOIN sys.foreign_key_columns AS fkc ON fkc.constraint_object_id = fk.object_id
JOIN sys.columns AS pc ON pc.object_id = fkc.parent_object_id     AND pc.column_id = fkc.parent_column_id
JOIN sys.columns AS rc ON rc.object_id = fkc.referenced_object_id AND rc.column_id = fkc.referenced_column_id
GROUP BY fk.name, fk.parent_object_id, fk.referenced_object_id,
         fk.delete_referential_action_desc, fk.update_referential_action_desc,
         fk.is_disabled, fk.is_not_trusted
ORDER BY child_table, constraint_name;

To see the relationships of a single table only, add WHERE fk.parent_object_id = OBJECT_ID(N'dbo.Orders') OR fk.referenced_object_id = OBJECT_ID(N'dbo.Orders'); that lists both the tables it references and the tables that reference it.

Delete and Update Rules

The on_delete and on_update columns show what happens when a parent row is deleted or its key changes: NO_ACTION rejects the operation, CASCADE deletes or updates the child rows too, and SET_NULL and SET_DEFAULT clear the child column or reset it to its default.

Untrusted Foreign Keys

When a foreign key is disabled with NOCHECK and re-enabled, or when checks are skipped during a bulk load, SQL Server can’t be sure the existing data follows the rule and marks the constraint is_not_trusted = 1. This isn’t only a data quality issue: the optimizer can’t use untrusted constraints when building plans. For example, it loses the ability to remove an unnecessary join from the plan (join elimination).

-- Untrusted or disabled foreign keys, with fix commands
SELECT OBJECT_SCHEMA_NAME(fk.parent_object_id) + N'.' + OBJECT_NAME(fk.parent_object_id) AS table_name,
       fk.name, fk.is_disabled, fk.is_not_trusted,
       N'ALTER TABLE ' + QUOTENAME(OBJECT_SCHEMA_NAME(fk.parent_object_id)) + N'.'
         + QUOTENAME(OBJECT_NAME(fk.parent_object_id))
         + N' WITH CHECK CHECK CONSTRAINT ' + QUOTENAME(fk.name) + N';' AS fix_command
FROM sys.foreign_keys AS fk
WHERE fk.is_not_trusted = 1 OR fk.is_disabled = 1;

Foreign Key Columns Without an Index

SQL Server doesn’t create indexes on foreign key columns automatically. When a parent row is deleted or its key updated, SQL Server has to find the matching child rows; without an index, it scans the whole child table. That can slow down a simple delete and cause blocking. The query below lists foreign keys whose first column isn’t the leading column of any index:

SELECT OBJECT_SCHEMA_NAME(fk.parent_object_id) + N'.' + OBJECT_NAME(fk.parent_object_id) AS table_name,
       fk.name AS constraint_name,
       c.name  AS first_fk_column
FROM sys.foreign_keys AS fk
JOIN sys.foreign_key_columns AS fkc
     ON fkc.constraint_object_id = fk.object_id AND fkc.constraint_column_id = 1
JOIN sys.columns AS c
     ON c.object_id = fkc.parent_object_id AND c.column_id = fkc.parent_column_id
WHERE NOT EXISTS (SELECT 1
                  FROM sys.index_columns AS ic
                  WHERE ic.object_id = fkc.parent_object_id
                    AND ic.column_id = fkc.parent_column_id
                    AND ic.key_ordinal = 1)
ORDER BY table_name;

Adding an index for every result isn’t automatic; each index adds write overhead to the table. For relationships where parent rows get deleted or that are joined often, an index usually pays off. For index design details, see our SQL Server index guide.

Sorting Tables into Dependency Order

When loading data, parent tables come first; when deleting, child tables come first. The recursive query below assigns each table a level: level 0 tables reference no other table. Load in ascending level order and delete in descending order.

WITH deps AS (
    SELECT DISTINCT parent_object_id AS child_id, referenced_object_id AS parent_id
    FROM sys.foreign_keys
    WHERE parent_object_id <> referenced_object_id      -- ignore self-references
),
levels AS (
    SELECT t.object_id, 0 AS lvl
    FROM sys.tables AS t
    WHERE NOT EXISTS (SELECT 1 FROM deps AS d WHERE d.child_id = t.object_id)
    UNION ALL
    SELECT d.child_id, l.lvl + 1
    FROM deps AS d
    JOIN levels AS l ON l.object_id = d.parent_id
    WHERE l.lvl < 50                                    -- safety net against cycles
)
SELECT OBJECT_SCHEMA_NAME(object_id) + N'.' + OBJECT_NAME(object_id) AS table_name,
       MAX(lvl) AS load_level
FROM levels
GROUP BY object_id
ORDER BY load_level, table_name;

Tables that reference each other in a cycle don’t get a level in this query; if some tables are missing from the list, examine the relationships between them separately.

Checklist

• All foreign key relationships and their rules are documented.
• Relationships with CASCADE are reviewed before bulk deletes.
• Untrusted and disabled constraints are fixed WITH CHECK in a maintenance window.
• Foreign key columns without an index are evaluated against the workload.
• Load and delete order is planned from the dependency levels.