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 CONSTRAINT does not relax the check, it disables the constraint. Measured on SQL Server 2025: straight after that statement is_disabled and is_not_trusted are both 1, and an INSERT of a child row whose parent does not exist goes straight in. The error is gone because nothing is being checked, and every row written while it is off is an orphan nobody has counted:

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

The other NOCHECK, WITH NOCHECK on an ADD CONSTRAINT, is the one people think they are using: it skips validating the existing rows and enforces new DML from then on. It still leaves the constraint untrusted, and an untrusted constraint is worse than no constraint, because the optimiser stops using it for join elimination while you still pay to maintain it. 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; if orphans went in while it was disabled, this is where they surface, as a 547 raised by the ALTER TABLE statement itself. A plain CHECK CONSTRAINT re-enables it but assumes WITH NOCHECK, so the constraint comes back enforced and still untrusted.

Microsoft’s reference covers ALTER TABLE, where the two meanings of NOCHECK are set out (WITH CHECK is assumed for new constraints, WITH NOCHECK for re-enabled ones, and the optimiser ignores a constraint defined WITH NOCHECK), and primary and foreign key constraints, including the 4 cascading actions, in full.

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.

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
• SQL Server Silent Failures, the instance-wide audit that lists untrusted constraints among the things that never raise an error
• Deleting Rows in Batches, the safe way to remove the child rows once you have found them