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
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
CHECKconstraint, 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
-
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'; -
For an insert, make sure the parent exists first, or insert parents before children.
SELECT * FROM dbo.Customers WHERE CustomerId = 42;
-
For a delete, remove or reassign the child rows first, or define the key with
ON DELETE CASCADEif deleting children with the parent is what you want.DELETE FROM dbo.Orders WHERE CustomerId = 42; DELETE FROM dbo.Customers WHERE CustomerId = 42;
-
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
2627Violation of PRIMARY KEY or UNIQUE KEY515Cannot insert the value NULL into column3726Can't drop: referenced by a FOREIGN KEY23503PostgreSQL: foreign key constraint violation23514PostgreSQL: check constraint violation1452MySQL: Foreign key fails: cannot add child row1451MySQL: Foreign key fails: cannot delete parent rowORA-02291Oracle: integrity constraint violated - no parent keyORA-02292Oracle: integrity constraint violated - child record found
More constraints and keys errors
2601Cannot insert duplicate key row1913Index or statistics name already exists1750Could not create constraint or index4712Can't truncate a referenced table1505Duplicate key found, unique index
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.
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.