Indexing Foreign Keys

You delete one parent row, and the statement just sits there. Indexing foreign keys is the step that got skipped. SQL Server enforces the relationship, but it does not create an index on the child-side column for you. A parent delete finds that gap at the least convenient moment.

Why the Child Key Matters When Indexing Foreign Keys

A foreign key ensures that child values refer to valid parent rows. When a parent row is deleted or its key changes, SQL Server must check for dependent child rows and apply the configured action. Without a useful child-side index, that check can scan a large child table. The scan can add latency and locks to a seemingly tiny parent change.

Joins from parent to child can benefit from the same index. That does not mean every foreign key needs a dedicated structure. A composite index whose leading columns match the foreign key can already serve the check. I inspect existing keys before creating another index with a familiar name. What happens to a parent delete when the child table becomes ten times larger?

Understand the Delete Path

Consider Customers and Orders, with Orders.CustomerID referencing Customers.CustomerID. Deleting one customer requires determining whether matching orders exist. NO ACTION blocks the delete if children remain. CASCADE deletes them. Both paths need to find those children. A suitable index on Orders.CustomerID can make the lookup focused rather than broad.

The impact is not limited to duration. Broad reads can increase lock exposure and interfere with concurrent work. Test the actual parent-row operation in a safe environment with representative child volume. A test customer with zero orders does not expose the cost of a customer with years of transactions.

Find Foreign Keys to Review

Catalog views can list the foreign key columns and their order. The following query shows single-column foreign keys whose child table lacks an enabled rowstore index starting with that column. It is intentionally narrow. Composite foreign keys and filtered or specialized indexes require a fuller definition comparison before any recommendation.

Use this as an investigation queue. It does not prove that adding an index improves the workload. A tiny child table can be cheaper to scan, and a write-heavy table pays for every new index. Decide from actual queries and parent changes.

SELECT SCHEMA_NAME(t.schema_id) AS child_schema,
       t.name AS child_table, fk.name AS foreign_key_name,
       c.name AS child_column
FROM sys.foreign_keys AS fk
JOIN sys.foreign_key_columns AS fkc
  ON fkc.constraint_object_id = fk.object_id
JOIN sys.tables AS t ON t.object_id = fk.parent_object_id
JOIN sys.columns AS c
  ON c.object_id = fkc.parent_object_id
 AND c.column_id = fkc.parent_column_id
WHERE (SELECT COUNT(*) FROM sys.foreign_key_columns AS x
       WHERE x.constraint_object_id = fk.object_id) = 1
  AND NOT EXISTS (
    SELECT 1
    FROM sys.indexes AS i
    JOIN sys.index_columns AS ic
      ON ic.object_id = i.object_id AND ic.index_id = i.index_id
    WHERE i.object_id = t.object_id AND i.is_disabled = 0
      AND i.type IN (1, 2) AND ic.key_ordinal = 1
      AND ic.column_id = c.column_id);

Check Composite Key Order

A foreign key can contain multiple columns. An index beginning with those columns in the same order can support an equality check on all of them. An index with a different leading column can still be useful for another query, but it will not provide the same direct seek for the foreign key values. Included columns do not replace key columns for seeking.

I compare the foreign key column list with index key order, then look at actual parent operations. A composite index can serve both the relationship and a common application query if its next columns match the access pattern. That shared role can be more economical than a separate index for each use.

Create the Narrow Useful Index

For the Customers and Orders example, a child-side index begins with CustomerID. Additional columns should be justified by common joins or filters, not added merely because they appear in a report. The sample index supports locating a customer’s orders and orders them by date. Check whether an existing index already starts with CustomerID before running it.

The related SELECT is a simple way to inspect the read plan. The parent delete requires a controlled test with data that can be safely changed or rolled back. Do not run destructive examples against live customer data.

CREATE INDEX IX_Orders_CustomerID_OrderDate
ON dbo.Orders (CustomerID, OrderDate);

SELECT OrderID, OrderDate
FROM dbo.Orders
WHERE CustomerID = 42
ORDER BY OrderDate DESC;

Measure Both Read and Write Effects

An index can improve a join and a parent delete while slowing order inserts. Measure both sides. Use actual execution plans and logical reads for representative joins, and record parent change duration under realistic concurrency. Compare insert throughput and transaction log growth before and after the index. A narrow key usually makes the tradeoff easier.

If the child table is small, the optimizer can prefer a scan even when the index exists. That is not proof the index is broken. It can be the cheapest plan. Reevaluate as data grows. I avoid forcing an index simply to see its name in a plan.

Include Cascades and Batch Changes

Cascading deletes can turn one parent statement into many child changes. The child-side index helps find rows, but the cascade still writes each affected row and its indexes. Large cascades need transaction size, log capacity, and blocking analysis. An index alone cannot make a huge delete operationally harmless.

For batch parent cleanup, process bounded groups and observe locks and duration. If a maintenance job deletes old parents, include it in testing. A foreign key check that looks minor in a single-row test can dominate a nightly batch. The application path and the maintenance path deserve the same attention.

Avoid Indexing Foreign Keys by Rule Alone

A common checklist says to index every foreign key. It is a useful prompt, not a final decision. An existing composite index can already provide the needed leading keys. A rarely modified parent of a tiny child table can not justify another write target. The index should answer a measured problem or protect a known operational path.

I record the relationship, supporting index, and relevant query or delete process. That record prevents a future cleanup from removing an index because its read counter appears quiet. Foreign key enforcement can use an index even when the business query count is low. Context matters more than the naming convention.

Revisit Indexing Foreign Keys as Tables Grow

A scan of a thousand child rows and a scan of a hundred million are different events. Growth can turn an acceptable unindexed check into a serious source of blocking. Track child table size, parent-row maintenance frequency, and query plans after major data changes. A new retention process can also change the value of the index.

The goal is predictable relationship checks with an acceptable cost on child writes. Review one relationship at a time and test the real operations. SQL Server guarantees the relationship either way. Indexing determines how much work it must do to keep that guarantee.

Related reading on this blog: Find Untrusted Foreign Key and Execution Plans and Indexing Strategies: Quick Guide.

Indexing foreign keys is not a schema decoration, it is a measured path to related rows.

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