// SQL Server error reference

SQL Server error 547: Statement conflicted with a constraint

An INSERT, UPDATE or DELETE broke a FOREIGN KEY or CHECK constraint: a child row without a parent, a parent that still has children, or a failed check.

Error number
547
Severity
16
Category
Constraints and keys
Error message
The %ls statement conflicted with the %ls constraint "%.*ls". The conflict occurred in database "%.*ls", table "%.*ls"%ls%.*ls%ls.

What it means

Error 547 covers two kinds of constraint. With a FOREIGN KEY, it means the statement would leave a reference pointing nowhere: inserting or updating a child row whose parent doesn't exist, or deleting or changing a parent that child rows still use. With a CHECK constraint, it means a row failed the constraint's condition. The message names the statement type, the constraint, and the database, table and column involved.

Common causes

  • Inserting a child row with a foreign key value that has no matching parent row (wrong id, or the parent isn't inserted yet).
  • Deleting a parent row, or changing its key, while child rows reference it.
  • A value that fails a CHECK constraint, such as a negative quantity.
  • Adding a foreign key or check constraint to a table whose existing data already violates it (ALTER TABLE ... ADD CONSTRAINT).
  • Loading tables in the wrong order: children before parents.

How to fix it

  1. Read the constraint name in the message, then see what it enforces.

    SELECT fk.name, OBJECT_NAME(fk.parent_object_id) AS child_table,
           OBJECT_NAME(fk.referenced_object_id) AS parent_table,
           fk.delete_referential_action_desc
    FROM sys.foreign_keys AS fk WHERE fk.name = N'FK_Orders_Customers';
  2. For an insert, make sure the parent exists first, or insert parents before children.

    SELECT * FROM dbo.Customers WHERE CustomerId = 42;
  3. For a delete, remove or reassign the child rows first, or define the key with ON DELETE CASCADE if deleting children with the parent is what you want.

    DELETE FROM dbo.Orders WHERE CustomerId = 42;
    DELETE FROM dbo.Customers WHERE CustomerId = 42;
  4. When adding a constraint to existing data, find the rows that break it.

    SELECT o.*
    FROM dbo.Orders AS o
    LEFT JOIN dbo.Customers AS c ON c.CustomerId = o.CustomerId
    WHERE o.CustomerId IS NOT NULL AND c.CustomerId IS NULL;

Reproduce it safely

Temp tables can't have foreign keys, so the repro uses a CHECK constraint; a FOREIGN KEY violation raises the same error number.

CREATE TABLE #p (id int PRIMARY KEY);
CREATE TABLE #c (id int, CHECK (id > 0));
INSERT INTO #c VALUES (-1);  -- Msg 547: The INSERT statement conflicted with the CHECK constraint ...
DROP TABLE #c; DROP TABLE #p;

Questions

How do I delete a row that other tables reference?

Delete or update the referencing rows first, or change the foreign key to ON DELETE CASCADE or ON DELETE SET NULL if that matches the data's meaning. Disabling the constraint leaves orphaned rows behind.

Related errors

More constraints and keys errors

The code and message text are from the Microsoft Database Engine events and errors reference. Browse the full SQL Server error list or search every database error.

// fewer errors, faster fixes

Work on SQL Server in SQLly

A native SQL client for macOS, Windows and Linux with schema-aware IntelliSense, guardrails for risky statements and clear error messages, for SQL Server and the other databases you use.