Foreign Key Checks in Insert Plans: The Cost of Each Constraint

Foreign key checks add work to every insert, because SQL Server must confirm each parent row exists. That work is small and visible in the plan, and it is the price of a database that cannot hold orphans.

The import that is “slowed down by foreign keys”

A developer says the nightly import is slow and suggests dropping the foreign keys. It is a fair thought, so measure before you answer. The demo has two parent tables and one child table that references both. The tables are small and plainly named, and the last block drops them.

DROP TABLE IF EXISTS dbo.OrderItemNoFk;
DROP TABLE IF EXISTS dbo.OrderItemDemo;
DROP TABLE IF EXISTS dbo.ProductDemo;
DROP TABLE IF EXISTS dbo.CustomerDemo;

CREATE TABLE dbo.CustomerDemo (Id int PRIMARY KEY);
CREATE TABLE dbo.ProductDemo (Id int PRIMARY KEY);

CREATE TABLE dbo.OrderItemDemo
(
    Id int PRIMARY KEY,
    CustomerId int NOT NULL,
    ProductId int NOT NULL,
    CONSTRAINT FK_OrderItemDemo_Customer FOREIGN KEY (CustomerId) REFERENCES dbo.CustomerDemo (Id),
    CONSTRAINT FK_OrderItemDemo_Product FOREIGN KEY (ProductId) REFERENCES dbo.ProductDemo (Id)
);

INSERT dbo.CustomerDemo VALUES (1);
INSERT dbo.ProductDemo VALUES (10);

Each foreign key points at a parent primary key, so each parent lookup has an index to use. Declaring a foreign key does not create an index on the child column. That is a separate decision.

See the checks in one insert

Insert one valid row with STATISTICS IO on. The messages list every table the statement touched.

SET STATISTICS IO ON;

INSERT dbo.OrderItemDemo VALUES (1, 1, 10);

SET STATISTICS IO OFF;

You see three tables, not one. ProductDemo shows 2 logical reads, CustomerDemo shows 2, and OrderItemDemo shows 3. The two parents were read only to prove they contain the keys.

Find the checks in the plan

Now insert a second valid row and print the plan as text. In a graphical plan, you would turn on the actual plan and click the operators instead.

SET STATISTICS PROFILE ON;

INSERT dbo.OrderItemDemo VALUES (2, 1, 10);

SET STATISTICS PROFILE OFF;

The text plan has an Assert at the top. Under it are two Nested Loops Left Semi Join operators, plus a Clustered Index Seek on each parent. That is one semi join per foreign key. Each returns one row, once.

The screenshots show the same thing in the properties window. The Assert is the gatekeeper. It raises an error if either semi join finds no parent.

What a missing parent does

BEGIN TRY
    INSERT dbo.OrderItemDemo VALUES (3, 999, 10);
END TRY
BEGIN CATCH
    SELECT ERROR_NUMBER() AS ErrorNumber;
END CATCH;

SELECT Id, CustomerId, ProductId
FROM dbo.OrderItemDemo
ORDER BY Id;

Customer 999 does not exist, so the insert fails with error 547. The table still holds only rows 1 and 2. That is the Assert doing its job.

What you save by removing the keys, and what you lose

Here is the same insert into a child table with no foreign keys.

CREATE TABLE dbo.OrderItemNoFk (Id int PRIMARY KEY, CustomerId int NOT NULL, ProductId int NOT NULL);

SET STATISTICS IO ON;

INSERT dbo.OrderItemNoFk VALUES (1, 1, 10);

SET STATISTICS IO OFF;

Only the child table is touched, with 3 logical reads. The two parent lookups are gone. For this tiny insert the difference is small, so measure on your own load before deciding anything. The cost grows with rows, but so does the risk of orphans.

The middle path is to disable one key during a load and bring it back afterward. Watch what that does to trust.

ALTER TABLE dbo.OrderItemDemo NOCHECK CONSTRAINT FK_OrderItemDemo_Customer;

INSERT dbo.OrderItemDemo VALUES (3, 999, 10);

SELECT name, is_disabled, is_not_trusted
FROM sys.foreign_keys
WHERE parent_object_id = OBJECT_ID('dbo.OrderItemDemo')
ORDER BY name;

BEGIN TRY
    ALTER TABLE dbo.OrderItemDemo WITH CHECK CHECK CONSTRAINT FK_OrderItemDemo_Customer;
END TRY
BEGIN CATCH
    SELECT ERROR_NUMBER() AS ErrorNumber;
END CATCH;

DELETE dbo.OrderItemDemo WHERE CustomerId = 999;

ALTER TABLE dbo.OrderItemDemo WITH CHECK CHECK CONSTRAINT FK_OrderItemDemo_Customer;

SELECT name, is_disabled, is_not_trusted
FROM sys.foreign_keys
WHERE parent_object_id = OBJECT_ID('dbo.OrderItemDemo')
ORDER BY name;

With the key disabled, the orphan row for customer 999 goes straight in, and the catalog shows is_disabled 1 and is_not_trusted 1. Re-enabling it WITH CHECK fails with error 547, because the orphan is still there. After I delete it, the key comes back with both flags at 0.

That is the lesson. Drop the check and you can get orphans. Re-enable it carelessly and the key stays untrusted.

DROP TABLE IF EXISTS dbo.OrderItemNoFk;
DROP TABLE IF EXISTS dbo.OrderItemDemo;
DROP TABLE IF EXISTS dbo.ProductDemo;
DROP TABLE IF EXISTS dbo.CustomerDemo;

Before you drop a foreign key for speed, look at what it is actually costing.

A foreign key check is not decorative overhead, it is work that preserves a declared relationship.

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