Nullable Foreign Keys: Optional Relationships and Outer Joins

Nullable foreign keys let a row say “no relationship yet” while still checking every value that is present. They are not broken data, but they change which join you should use.

The report that lost its orders

A manager asks why the order report shows fewer orders than the order table. The query joins orders to sales reps. Some orders have no rep yet, and the join quietly dropped them.

The design is fine. An order can wait for a rep, so RepId is allowed to be NULL. The mistake is in the read path. Let me build the pattern and watch it. The demo uses two small tables and removes them at the end.

DROP TABLE IF EXISTS dbo.OrderDemo;
DROP TABLE IF EXISTS dbo.RepDemo;

CREATE TABLE dbo.RepDemo (RepId int PRIMARY KEY, RepName nvarchar(50) NOT NULL);

CREATE TABLE dbo.OrderDemo
(
    OrderId int PRIMARY KEY,
    RepId   int NULL REFERENCES dbo.RepDemo (RepId)
);

INSERT dbo.RepDemo (RepId, RepName) VALUES (1, N'Alex');
INSERT dbo.OrderDemo (OrderId, RepId) VALUES (10, 1), (11, NULL);

Order 10 belongs to Alex. Order 11 has no rep. The foreign key accepted both.

What the constraint checks

A NULL means “no rep”, and SQL Server does not look for a parent. But a value that is present must exist. Try an order for rep 999.

INSERT dbo.OrderDemo (OrderId, RepId) VALUES (12, 999);

SELECT COUNT(*) AS order_count FROM dbo.OrderDemo;

This fails with error 547, a foreign key conflict, and the count is still 2. Do not be tempted to store 0 for “no rep” so the column can be NOT NULL. You would need a fake rep 0, and your reports would show a person who does not exist.

The constraint checks that a parent exists. It does not decide when an order must have a rep, or who may own it. Those are business rules.

Left join versus inner join

Now the report. Same two tables, two join types.

SELECT N'left join' AS join_kind, o.OrderId, o.RepId, r.RepName
FROM dbo.OrderDemo AS o
LEFT JOIN dbo.RepDemo AS r ON r.RepId = o.RepId
ORDER BY o.OrderId;

SELECT N'inner join' AS join_kind, o.OrderId, o.RepId, r.RepName
FROM dbo.OrderDemo AS o
JOIN dbo.RepDemo AS r ON r.RepId = o.RepId
ORDER BY o.OrderId;

The left join returns both orders, with NULL for the missing rep name. The inner join returns only order 10. That missing row is the missing orders from the story.

Be careful when you count. COUNT(*) counts every row. COUNT on a column from the right side counts only the matches.

SELECT COUNT(*) AS all_orders, COUNT(r.RepId) AS assigned_orders
FROM dbo.OrderDemo AS o
LEFT JOIN dbo.RepDemo AS r ON r.RepId = o.RepId;

The answer is 2 and 1. Name your measures so the reader knows whether unassigned orders are in the number.

WHERE can undo your LEFT JOIN

Add a second rep and an order for that rep. Then filter for Alex in two different places.

INSERT dbo.RepDemo (RepId, RepName) VALUES (2, N'Sam');
INSERT dbo.OrderDemo (OrderId, RepId) VALUES (13, 2);

SELECT N'filter in WHERE' AS version, o.OrderId, r.RepName
FROM dbo.OrderDemo AS o
LEFT JOIN dbo.RepDemo AS r ON r.RepId = o.RepId
WHERE r.RepName = N'Alex'
ORDER BY o.OrderId;

SELECT N'filter in ON' AS version, o.OrderId, r.RepName
FROM dbo.OrderDemo AS o
LEFT JOIN dbo.RepDemo AS r ON r.RepId = o.RepId AND r.RepName = N'Alex'
ORDER BY o.OrderId;

The WHERE version returns one row, order 10. The unassigned order is gone again, and so is Sam’s, so the LEFT JOIN now behaves like an inner join. The ON version returns all three orders and shows a name only where it is Alex. Put the filter in ON when you want to keep every order.

When the optional key has two columns

A composite key adds a trap. If one column is NULL, SQL Server skips the check for the whole key.

DROP TABLE IF EXISTS dbo.AddressDemo;
DROP TABLE IF EXISTS dbo.RegionDemo;

CREATE TABLE dbo.RegionDemo
(
    CountryCode char(2) NOT NULL,
    RegionCode  char(2) NOT NULL,
    PRIMARY KEY (CountryCode, RegionCode)
);
INSERT dbo.RegionDemo VALUES ('US', 'CA');

CREATE TABLE dbo.AddressDemo
(
    AddressId   int PRIMARY KEY,
    CountryCode char(2) NULL,
    RegionCode  char(2) NULL,
    FOREIGN KEY (CountryCode, RegionCode) REFERENCES dbo.RegionDemo
);

INSERT dbo.AddressDemo (AddressId, CountryCode, RegionCode) VALUES (1, 'ZZ', NULL);

SELECT AddressId, CountryCode, RegionCode FROM dbo.AddressDemo;

The insert succeeds, even though there is no country ZZ. The half-empty key was never checked. If you want all parts or none, say so with a CHECK constraint.

DELETE dbo.AddressDemo;

ALTER TABLE dbo.AddressDemo ADD CONSTRAINT CK_AddressDemo_Region
CHECK ((CountryCode IS NULL AND RegionCode IS NULL)
    OR (CountryCode IS NOT NULL AND RegionCode IS NOT NULL));

INSERT dbo.AddressDemo (AddressId, CountryCode, RegionCode) VALUES (1, 'ZZ', NULL);

Now the same insert fails with error 547, this time from the CHECK constraint. Last, remember that a foreign key does not create an index on the referencing column. Add one only when your queries need it.

DROP TABLE IF EXISTS dbo.AddressDemo;
DROP TABLE IF EXISTS dbo.RegionDemo;
DROP TABLE IF EXISTS dbo.OrderDemo;
DROP TABLE IF EXISTS dbo.RepDemo;

Next time a report comes up short, check the join before you blame the data.

A nullable foreign key is not broken data, it is a relationship allowed to be absent.

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