The DELETE Statement Conflicted With a FOREIGN KEY Constraint (Error 547)

Read the message before you touch anything, because it names the table and column that is in the way. That is unusually generous for a SQL Server error, and most of the time it is the whole answer. What it does not tell you is which direction the problem runs in, and there are two.

Which Direction Are You In

The word in the message tells you which. A FOREIGN KEY conflict means you are writing a child row whose parent is absent. A REFERENCE conflict means you are removing a parent that children still depend on.

Insert Side: The Parent Is Not There

SELECT ol.OrderId
FROM   dbo.OrderLines AS ol
WHERE  ol.OrderId = 4711
  AND  NOT EXISTS (SELECT 1 FROM dbo.Orders AS o WHERE o.OrderId = ol.OrderId);

For a batch insert, find every offending value in one pass rather than one row at a time:

SELECT DISTINCT s.OrderId
FROM   #Staging AS s
LEFT   JOIN dbo.Orders AS o ON o.OrderId = s.OrderId
WHERE  o.OrderId IS NULL;

The fix is order of operations, not the constraint. Insert parents first, then children, inside one transaction.

Delete Side: Find the Children

SELECT OBJECT_NAME(fk.parent_object_id)  AS child_table,
       COL_NAME(fkc.parent_object_id, fkc.parent_column_id) AS child_column,
       OBJECT_NAME(fk.referenced_object_id) AS parent_table,
       fk.name AS constraint_name,
       fk.delete_referential_action_desc
FROM   sys.foreign_keys AS fk
JOIN   sys.foreign_key_columns AS fkc ON fkc.constraint_object_id = fk.object_id
WHERE  fk.referenced_object_id = OBJECT_ID(N'dbo.Orders');

That lists every table that can block a delete on dbo.Orders, which matters because the error only reports the first one it hit. On a wide schema you can fix that constraint and immediately meet the next.

Then delete the children first, or reassign them, inside a transaction.

The NOCHECK Misconception

This is the fix people reach for, and it does not do what they think.

ALTER TABLE dbo.OrderLines NOCHECK CONSTRAINT FK_OrderLines_Orders;

NOCHECK stops the constraint being validated against existing data. It does not stop it being enforced on new DML in the way most people expect, and it leaves the constraint marked untrusted:

SELECT name, is_disabled, is_not_trusted
FROM   sys.foreign_keys
WHERE  parent_object_id = OBJECT_ID(N'dbo.OrderLines');

An untrusted constraint is worse than no constraint, because the optimiser stops using it for join elimination while you still pay to maintain it, and you now have orphan rows nobody has counted. If you genuinely disable one, re-enable it with a check afterwards:

ALTER TABLE dbo.OrderLines WITH CHECK CHECK CONSTRAINT FK_OrderLines_Orders;

That second CHECK is not a typo. WITH CHECK CHECK CONSTRAINT validates the existing rows and clears the untrusted flag.

Cascades, and Why They Are Not a Default

ON DELETE CASCADE makes the error disappear by deleting the children for you. That is correct for a genuine parent-child ownership relationship, and dangerous everywhere else, because one delete on a lookup table can quietly remove millions of rows. Cascades also cannot form cycles, and they make the blast radius of a mistaken delete invisible at the call site.

Decide it deliberately per relationship, never as a way of silencing 547.

Related Scripts

• Get Schema Change History, prove what changed on an instance, and when
• SQL Server Change Management, what to capture before and after a schema change
• Deleting Rows in Batches, the safe way to remove the child rows once you have found them

Common Questions

Related Scripts

• Get Schema Change History, prove what changed on an instance, and when
• SQL Server Change Management, what to capture before and after a schema change
• Deleting Rows in Batches, the safe way to remove the child rows once you have found them