Sam

Posted on Oct 5 Originally published at woodfireerd.com

The ALTER TABLE statement conflicted with the FOREIGN KEY constraint

#database #debugging #sql

You add a foreign key to a table that has been live for years, and SQL Server refuses:

ALTER TABLE dbo.SalesOrder ADD CONSTRAINT FK_SalesOrder_Customer
    FOREIGN KEY (CustomerId) REFERENCES dbo.Customer (CustomerId);

Msg 547, Level 16, State 1, Line 1
The ALTER TABLE statement conflicted with the FOREIGN KEY constraint "FK_SalesOrder_Customer".
The conflict occurred in database "erd_article_test", table "dbo.Customer", column 'CustomerId'.

Read that message carefully, because it sends most people to the wrong table. It names dbo.Customer and column CustomerId — the table being pointed at. There is nothing wrong with any row in it. The rows at fault are in dbo.SalesOrder: they hold a CustomerId that Customer does not have.

Find the rows

SELECT o.OrderId, o.CustomerId
FROM   dbo.SalesOrder AS o
WHERE  o.CustomerId IS NOT NULL
  AND  NOT EXISTS (SELECT 1 FROM dbo.Customer AS c WHERE c.CustomerId = o.CustomerId)
ORDER BY o.OrderId;

OrderId  CustomerId
-------  ----------
11       99
13       404

IS NOT NULL is not optional. A null foreign key means "points at nothing", which a foreign key allows; without that line you get every unlinked row back and the list is useless.

If you only want to know the size of the problem before deciding anything:

SELECT COUNT(*) AS Orphans
FROM   dbo.SalesOrder AS o
WHERE  o.CustomerId IS NOT NULL
  AND  NOT EXISTS (SELECT 1 FROM dbo.Customer AS c WHERE c.CustomerId = o.CustomerId);

Three honest fixes

Create the missing parents if the values mean something — a customer deleted by hand, a load that ran out of order. The orders keep their history.

INSERT dbo.Customer (CustomerId)
SELECT DISTINCT o.CustomerId
FROM   dbo.SalesOrder AS o
WHERE  o.CustomerId IS NOT NULL
  AND  NOT EXISTS (SELECT 1 FROM dbo.Customer AS c WHERE c.CustomerId = o.CustomerId);

Set them to null if the link is wrong but the row is real. The order survives, pointing at nobody, which is exactly what the data is telling you:

UPDATE dbo.SalesOrder
SET    CustomerId = NULL
WHERE  CustomerId IS NOT NULL
  AND  NOT EXISTS (SELECT 1 FROM dbo.Customer AS c WHERE c.CustomerId = dbo.SalesOrder.CustomerId);

Delete them only when you know the rows are rubbish. Check what points at them first, or you will be reading this article again about the next table down.

WITH NOCHECK, and what it costs

There is a fourth option, and it is the one reached for under time pressure:

ALTER TABLE dbo.SalesOrder WITH NOCHECK ADD CONSTRAINT FK_SalesOrder_Customer
    FOREIGN KEY (CustomerId) REFERENCES dbo.Customer (CustomerId);

That succeeds. The existing rows are not examined, and the bad ones stay exactly as they are.

What it buys you is real: from now on, nothing new can be inserted that breaks the rule.

INSERT dbo.SalesOrder (OrderId, CustomerId) VALUES (14, 777);

Msg 547, Level 16, State 1, Line 1
The INSERT statement conflicted with the FOREIGN KEY constraint "FK_SalesOrder_Customer".

What it costs is that the key is not trusted:

SELECT name, is_not_trusted, is_disabled
FROM   sys.foreign_keys
WHERE  name = 'FK_SalesOrder_Customer';

name                     is_not_trusted  is_disabled
-----------------------  --------------  -----------
FK_SalesOrder_Customer   1               0

is_not_trusted = 1 means SQL Server is enforcing the rule but will not believe it when building a query plan. It cannot eliminate the join to Customer in a query that only needs columns from SalesOrder, because as far as it knows there are rows in there with no parent — and there are. The constraint documents an intention the data does not meet.

Worth knowing: a key you never fix stays untrusted forever, and nothing warns you. This finds them all:

SELECT OBJECT_SCHEMA_NAME(parent_object_id) AS SchemaName,
       OBJECT_NAME(parent_object_id)        AS TableName,
       name                                 AS ForeignKey
FROM   sys.foreign_keys
WHERE  is_not_trusted = 1 AND is_disabled = 0;

Making it trusted later

Once the rows are sorted out, ask SQL Server to check what it skipped:

ALTER TABLE dbo.SalesOrder WITH CHECK CHECK CONSTRAINT FK_SalesOrder_Customer;

The doubled CHECK CHECK is not a typo: the first says check the existing rows, the second names the constraint to enable. Run it while bad rows are still there and you get Msg 547 again — the same message, for the same reason. Run it after they are gone and is_not_trusted goes to 0.

This is the step that gets forgotten, because the constraint already looks right in every tool that lists it. Add it to the migration script that does the cleanup, not to a note for later.

The part worth doing first

Before adding the key at all, know what the table is already joined to and what is pointing at it — a foreign key's effect is on the other table, so reading the table you are changing tells you very little.

That is the question WoodFireERD answers: pick a table, see what it points at and what points at it, and have the relationship come out as an ordered script, with the rows that would break it listed before anything runs.

Originally published at woodfireerd.com. Every statement in it was run against a real SQL Server before publishing — the error messages are copied from the output, not from memory.

DEV Community