Why Foreign Key Columns Need Indexes in SQL Server
Many developers assume that creating a foreign key also creates an index on the column. In SQL Server it does not. A primary key or unique constraint automatically gets an index; a foreign key does not. Missing foreign key indexes are one of the most common causes of slow deletes, slow joins and unexpected blocking.
Example
CREATE TABLE dbo.PayrollLine (
PayrollLineId BIGINT IDENTITY PRIMARY KEY,
PayrollRunId INT NOT NULL
CONSTRAINT FK_PayrollLine_PayrollRun REFERENCES dbo.PayrollRun (PayrollRunId),
EmployeeId INT NOT NULL
CONSTRAINT FK_PayrollLine_Employee REFERENCES dbo.Employee (EmployeeId),
Amount DECIMAL(18,2) NOT NULL
);Here PayrollLineId is indexed (primary key), but PayrollRunId and EmployeeId are not.
Problem 1: deleting or updating parent rows
When you delete a row from dbo.PayrollRun, SQL Server must check that no PayrollLine rows still reference it. Without an index on PayrollLine.PayrollRunId, that check scans the entire child table, once per deleted parent row. On a table with millions of lines, deleting one payroll run can take minutes and hold locks on the child table the whole time, blocking other users.
Problem 2: joins and lookups
Almost every query that reads children by parent, such as "all lines for this run" or "all payments for this employee", filters or joins on the foreign key column. Without an index, those queries scan as well.
Problem 3: cascading actions
With ON DELETE CASCADE, deleting a parent deletes its children automatically. Without an index, finding those children means scanning, and the cascade can lock large parts of the child table.
Find foreign keys without a supporting index
This query lists foreign keys where no index starts with the foreign key's first column (good enough for most single-column keys):
SELECT
OBJECT_NAME(fk.parent_object_id) AS child_table,
fk.name AS foreign_key,
COL_NAME(fkc.parent_object_id, fkc.parent_column_id) AS fk_column,
OBJECT_NAME(fk.referenced_object_id) AS parent_table
FROM sys.foreign_keys fk
JOIN sys.foreign_key_columns fkc
ON fkc.constraint_object_id = fk.object_id
AND fkc.constraint_column_id = 1
WHERE NOT EXISTS (
SELECT 1
FROM sys.index_columns ic
WHERE ic.object_id = fkc.parent_object_id
AND ic.column_id = fkc.parent_column_id
AND ic.key_ordinal = 1
)
ORDER BY child_table, foreign_key;Create the index
CREATE NONCLUSTERED INDEX IX_PayrollLine_PayrollRunId
ON dbo.PayrollLine (PayrollRunId);
CREATE NONCLUSTERED INDEX IX_PayrollLine_EmployeeId
ON dbo.PayrollLine (EmployeeId);If queries usually filter on the foreign key plus another column, put both in the index (foreign key first) and INCLUDE commonly selected columns, so one index serves both the constraint check and the queries.
Are there exceptions? A tiny child table, or one whose parent rows are never deleted and which you never query by parent, may not need the index. Those are rare; indexing foreign keys is the right default.
Bonus: untrusted foreign keys
If a foreign key was disabled, or created or re-enabled WITH NOCHECK, SQL Server marks it as not trusted. The optimizer then cannot rely on it, for example to skip a join it knows is unnecessary.
-- Find untrusted foreign keys
SELECT OBJECT_NAME(parent_object_id) AS table_name, name
FROM sys.foreign_keys
WHERE is_not_trusted = 1;
-- Re-validate (checks existing rows, so run it in a quiet period)
ALTER TABLE dbo.PayrollLine WITH CHECK CHECK CONSTRAINT FK_PayrollLine_PayrollRun;Quick checklist
- Remember: foreign keys are not indexed automatically.
- Run the query above and index foreign keys used in deletes or joins.
- Combine the foreign key with common filter columns where it helps.
- Re-validate untrusted foreign keys with
WITH CHECK CHECK CONSTRAINT.
Related reading:
Comments
Post a Comment