Disable for the load, then re-enable with validation. The doubled CHECK is required. Audit for constraints left untrusted by an earlier load. On an established database this one is worth running today. If re-enabling fails, the loaded data genuinely violates the constraint — find the orphans rather than forcing it.

SQL Server · error reference

SQL Server error 547: FOREIGN KEY constraint conflict

The INSERT statement conflicted with the FOREIGN KEY constraint. The conflict occurred in database "db", table "dbo.parent", column 'id'.

SQL Server's foreign-key violation, and the NOCHECK trap that makes it look solved when it is not — a constraint re-enabled without WITH CHECK is left untrusted.

What the database is telling you

The statement would break a foreign key: either an inserted child has no parent, or a deleted parent still has children. Error 547 covers both directions, and the message names the constraint and column.

Why it shows up in a migration

The standard fix is to switch constraints off for the load with NOCHECK CONSTRAINT ALL and switch them back afterwards. The trap is in the second half. ALTER TABLE ... CHECK CONSTRAINT ALL re-enables the constraint for future writes but does not validate the rows already loaded, and leaves it flagged untrusted — is_not_trusted = 1. The constraint looks enabled in every UI, does not vouch for the data you just inserted, and is quietly excluded from query optimisation. You need WITH CHECK CHECK, which reads like a typo and is not.

Common causes

Tables loaded out of dependency order, or in parallel.
A foreign key cycle that no ordering can satisfy.
Orphaned rows in the source that its own constraints never enforced.
Constraints re-enabled without WITH CHECK, so the violation surfaces later on an unrelated write.

How to fix it

Disable for the load, then re-enable with validation. The doubled CHECK is required.

EXEC sp_msforeachtable 'ALTER TABLE ? NOCHECK CONSTRAINT ALL';
-- load data --
EXEC sp_msforeachtable 'ALTER TABLE ? WITH CHECK CHECK CONSTRAINT ALL';

2
Audit for constraints left untrusted by an earlier load. On an established database this one is worth running today.
```
SELECT OBJECT_NAME(parent_object_id) AS [table], name
  FROM sys.foreign_keys
 WHERE is_not_trusted = 1;
```
3
If re-enabling fails, the loaded data genuinely violates the constraint — find the orphans rather than forcing it.
```
SELECT c.* FROM child c
  LEFT JOIN parent p ON p.id = c.parent_id
 WHERE c.parent_id IS NOT NULL AND p.id IS NULL;
```

What DBShifts does about it

DBShifts re-enables SQL Server constraints with WITH CHECK CHECK, which validates the rows just loaded. Because that validation can legitimately fail, a failure is not left as a log line: it is collected as a migration warning and surfaced to the caller, since the alternative is an unenforced constraint sitting beside a success message.

Bulk transfer engine

Migrating between engines?

DBShifts converts the schema, moves the data, and then proves the two sides match with a type-aware checksum per table — not just a row count. Errors like this one are handled on the way, and the ones that cannot be handled are reported rather than logged and forgotten.

Start free See the migration paths

Related errors

Oracle

ORA-02291: parent key not found during a data load

A child row arrived before its parent. Why load order alone will not save you, and how deferred constraints turn an unsolvable ordering problem into a solved one.

Oracle

ORA-02292: child record found when deleting

The mirror image of ORA-02291 — you deleted a parent that still has children. Common in CDC replication, where a delete can arrive before the child deletes that preceded it.

← All database errors