Voluntary DBA

This is more of a reminder/warning/spread-the-word post to be really, really careful when dealing with multi-column foreign key relationships when one or more of the foreign columns allows NULL values.

Things… may not work as you might expect.

Consider the following script:

CREATE TABLE PrimaryTable
(
	Col1 int NOT NULL,
	Col2 int NOT NULL,
	
	CONSTRAINT PK_PrimaryTable
		PRIMARY KEY(Col1, Col2)
);

CREATE TABLE ForeignTable
(
	Id int IDENTITY PRIMARY KEY,
	Col1 int NOT NULL,
	Col2 int NULL,
	
	CONSTRAINT FK_ForeignTable_PrimaryTable
		FOREIGN KEY(Col1, Col2) REFERENCES PrimaryTable(Col1, Col2)
);

INSERT INTO PrimaryTable(Col1, Col2)
	VALUES (0, 0), (1, 1);


-- Statement 1
INSERT INTO ForeignTable(Col1, Col2)
	VALUES (0, 0);

-- Statement 2
INSERT INTO ForeignTable(Col1, Col2)
	VALUES (0, 1);

-- Statement 3
INSERT INTO ForeignTable(Col1, Col2)
	VALUES (0, NULL);

Statement 1 is standard, and succeeds as we’d expect. Similarly as expected, Statement 2 fails with a constraint violation as there’s no row in PrimaryTable that has that combination of values.

What about Statement 3? If there was no foreign key, (0, NULL) would be an allowed combination of values, but what happens when there is a foreign key?

The answer is that the insert still succeeds.

I had expected it would fail just like Statement 2, because there isn’t a (0, NULL) combination in PrimaryTable. In fact, it’s not even an allowed combination to put in that table at all!

What happens is that NULL values are completely ignored from checking, and the constraint is satisfied as long as the non-NULL values match up to at least 1 row in the primary table. Edit: this is incorrect. If there are any NULLs in the foreign table columns, the constraint is not checked at all, which is even more perilous.

Multi-column foreign keys aren’t exactly the best practice in the world as far as schema design goes, but you do encounter them in the wild occasionally. If a database is suspect from an integrity point-of-view (which usually means it has many NULLable columns), be aware of this behaviour when doing data cleanup or querying. The corollary is that if this kind of schema is found in a database, poor integrity may not be very far away either.

Add this to your own Blitz script:

SELECT
	fk.name AS ConstraintName,
	COUNT(*) AS ColumnCount
	FROM sys.foreign_keys fk
	INNER JOIN sys.foreign_key_columns fkc ON fkc.constraint_object_id = fk.object_id
	GROUP BY fk.name
	HAVING COUNT(*) >= 2;