Foreign Keys Without Indexes: Finding and Fixing Slow Deletes

Deleting a single parent row can require checking a very large child table. Foreign keys without indexes are a practical place to investigate when that check consumes far more work than the deletion itself.

Understand the Child-Side Check

A foreign key protects the relationship between child values and an eligible parent key. SQL Server must enforce that relationship when a referenced parent key is deleted or updated. It needs to determine whether child rows reference the affected value, or locate those rows for a configured cascading action.

The foreign key declaration does not automatically create an index on the child columns. A primary or unique key supporting the parent side serves a different purpose. Without an appropriate child access path, the check can scan substantial child data, including when there are no matching rows and the deletion is ultimately allowed.

I investigate the relationship checks in the actual execution plan rather than assuming the parent table is slow. A tiny parent operation can carry a large child-side obligation. The operation's row count alone does not describe that obligation, and a foreign key's existence does not prove its supporting access path is present.

Find Foreign Keys Without Indexes in the Catalog

The following read-only query checks enabled foreign keys against enabled, unfiltered rowstore indexes on the referencing table. For a composite relationship, all foreign-key columns must occupy the index's leading key positions. They can be ordered differently for an equality check; included columns do not satisfy that requirement.

SELECT OBJECT_SCHEMA_NAME(fk.parent_object_id) AS ChildSchema,
       OBJECT_NAME(fk.parent_object_id) AS ChildTable,
       fk.name AS ForeignKeyName, fk.is_not_trusted,
       OBJECT_SCHEMA_NAME(fk.referenced_object_id) AS ParentSchema,
       OBJECT_NAME(fk.referenced_object_id) AS ParentTable
FROM sys.foreign_keys AS fk
CROSS APPLY
(
    SELECT COUNT(*) AS ForeignKeyColumnCount
    FROM sys.foreign_key_columns AS fc
    WHERE fc.constraint_object_id = fk.object_id
) AS n
WHERE fk.is_disabled = 0
  AND NOT EXISTS
  (
      SELECT 1 FROM sys.indexes AS 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 AS ic
            JOIN sys.foreign_key_columns AS fc
              ON fc.constraint_object_id = fk.object_id
             AND fc.parent_column_id = ic.column_id
            WHERE ic.object_id = i.object_id AND ic.index_id = i.index_id
              AND ic.key_ordinal BETWEEN 1 AND n.ForeignKeyColumnCount
        ) = n.ForeignKeyColumnCount
  );

Review foreign keys without indexes together with the operations that touch their parent keys. Treat the output as candidates for review, not automatic index creation. A small child table or a rarely changed parent can justify a different tradeoff. Specialized storage needs its own analysis. This report deliberately excludes filtered indexes because they do not generally cover every row the relationship check needs to examine.

Build a Rehearsal Case for Foreign Keys Without Indexes

Use a scratch database and fresh object names for the example. It requires SQL Server 2022 with compatibility level 160 or higher for GENERATE_SERIES. The parent includes a key without child rows, allowing an eligible delete whose relationship check can be inspected without removing populated relationships.

CREATE TABLE dbo.CustomerAccount
(
    CustomerID int NOT NULL PRIMARY KEY
);
CREATE TABLE dbo.CustomerOrderLine
(
    LineID int NOT NULL PRIMARY KEY,
    CustomerID int NOT NULL,
    CONSTRAINT FK_CustomerOrderLine_CustomerAccount FOREIGN KEY (CustomerID)
        REFERENCES dbo.CustomerAccount(CustomerID)
);
INSERT dbo.CustomerAccount (CustomerID)
SELECT value FROM GENERATE_SERIES(1,1001);
INSERT dbo.CustomerOrderLine (LineID,CustomerID)
SELECT value, ((value - 1) % 1000) + 1
FROM GENERATE_SERIES(1,100000);

Those counts define synthetic input, not an observed production workload or benchmark result. The child's primary key begins with LineID, so it does not provide a CustomerID-leading lookup path. Check existing objects before running the setup; this is a fresh-lab creation script, not a migration for an established application schema.

Inspect the Plan Before Adding an Index

Enable an actual execution plan in SSMS, then run the controlled transaction. The rollback preserves the synthetic parent row so the second comparison can use the same key. Review the child access path, logical reads, locking behavior, and relevant plan predicates. Keep the collection window and input unchanged.

SET STATISTICS IO ON;
BEGIN TRANSACTION;
DELETE dbo.CustomerAccount WHERE CustomerID = 1001;
ROLLBACK TRANSACTION;
SET STATISTICS IO OFF;

Expect to investigate a child-side check, but record what your plan actually uses. Estimates, data distribution, and available indexes influence the chosen operators. Do not write a saved cost or duration into the article as though every reader will observe it. A repeatable experiment is more useful than an impressive number with no execution evidence.

Fix Foreign Keys Without Indexes With a Narrow Key

An index beginning with the child foreign-key column gives the optimizer a direct access path for the equality search. Start narrow unless other workload requirements justify additional key or included columns. An index for finding referencing rows does not require copying every payload column into its leaf level.

CREATE INDEX IX_CustomerOrderLine_CustomerID
ON dbo.CustomerOrderLine(CustomerID);
SET STATISTICS IO ON;
BEGIN TRANSACTION;
DELETE dbo.CustomerAccount WHERE CustomerID = 1001;
ROLLBACK TRANSACTION;
SET STATISTICS IO OFF;

I compare the actual plans and reads after the change, including whether the relationship check seeks through the new index. Keep any remaining scan in context rather than declaring failure from its name alone. Other work in the statement can still scan for a different reason, especially with cascades or broader predicates.

Put Composite Columns in the Leading Key

For a relationship on CustomerID and InvoiceID, an index beginning with unrelated CreatedAt does not offer the same complete leading-key equality access. Putting the relationship columns only in INCLUDE does not solve that issue either. Both need to be key columns ahead of unrelated trailing keys for this candidate test.

Choose their order with the wider query workload in mind. An index on CustomerID, InvoiceID can also support searches on CustomerID alone. Reversing them supports a different partial-key search pattern while still allowing equality on both values. Do not insist that foreign-key declaration order is the only valid supporting order.

Check for an existing wider index whose leading keys already cover the relationship before adding another. Consolidation can reduce write overhead, but changing an established index needs plan review for its other consumers. Foreign keys without indexes identify an access-path question; they do not prescribe one index per constraint regardless of overlap.

Validate the Benefit and Retain Integrity

Which parent operations occur frequently enough to justify the added maintenance? Include parent updates, deletes, and configured cascades in the review. Child inserts and updates now maintain another index, so measure representative writes as well as the parent check. A useful read path still has a storage and write cost.

Review blocked deletes and long transactions separately. An index can reduce the search footprint but cannot make an invalid relationship change legal or eliminate every lock conflict. Keep the foreign key enabled and verify its trust state through an approved integrity process. Disabling the rule to obtain a quicker delete solves the timing question by discarding the data guarantee.

Retain the accepted index only after the rehearsal demonstrates its intended access path and production review approves the change. A relationship check should be efficient, visible in the plan, and faithful to the same integrity rule before and after tuning.

Related reading on this blog: Indexing for Delete: SQL in Sixty Seconds #197 and Find Untrusted Foreign Key.

A foreign-key index is not a replacement for integrity, it is an access path that helps SQL Server enforce it efficiently.

Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.