SQL Server: 'The INSERT statement conflicted with the FOREIGN KEY constraint' (error 547)

The INSERT statement conflicted with the FOREIGN KEY constraint "FK_Orders_Customers". (error 547) means the row points at a parent that does not exist. The rest of the message names the parent table and column, so check that value there: insert the parent first, or fix the value you are sending.

Its twin on delete, The DELETE statement conflicted with the REFERENCE constraint, is the same rule from the other side: the parent still has children. Delete the children first in one transaction, or declare ON DELETE CASCADE where the children cannot live without their parent.

The error

PS> .\FixLab.exe 'Server=(localdb)\MSSQLLocalDB;Database=fix7_lab;Integrated Security=true' "INSERT INTO dbo.Orders (CustomerId, Total) VALUES (99, 30.00)"
ex.Number = 547, ex.Class = 16, ex.State = 0
Microsoft.Data.SqlClient.SqlException: The INSERT statement conflicted with the FOREIGN KEY constraint "FK_Orders_Customers". The conflict occurred in database "fix7_lab", table "dbo.Customers", column 'Id'.
The statement has been terminated.

PS> .\FixLab.exe 'Server=(localdb)\MSSQLLocalDB;Database=fix7_lab;Integrated Security=true' "DELETE FROM dbo.Customers WHERE Id = 1"
ex.Number = 547, ex.Class = 16, ex.State = 0
Microsoft.Data.SqlClient.SqlException: The DELETE statement conflicted with the REFERENCE constraint "FK_Orders_Customers". The conflict occurred in database "fix7_lab", table "dbo.Orders", column 'CustomerId'.
The statement has been terminated.

Why it happens

dbo.Orders.CustomerId was declared with CONSTRAINT FK_Orders_Customers REFERENCES dbo.Customers (Id) in the test database fix7_lab. Customers 1, 2 and 3 existed; 99 did not, so the insert was refused. Read the "conflict occurred" part carefully: on an insert it names the parent table (where the value is missing), on a delete it names the child table (where rows still point at the parent). Customer 1 had two orders, so deleting it would have left them orphaned, and the default rule, NO ACTION, refuses that.

Error 547 is not only about foreign keys. A CHECK constraint failure carries the same number, with "conflicted with the CHECK constraint" in the text, so read the message rather than the number alone.

The fix

PS> .\FixLab.exe 'Server=(localdb)\MSSQLLocalDB;Database=fix7_lab;Integrated Security=true' "BEGIN TRAN; INSERT INTO dbo.Customers (Name, Email) VALUES ('Kwame Mensah', 'kwame.mensah@example.test'); INSERT INTO dbo.Orders (CustomerId, Total) VALUES (SCOPE_IDENTITY(), 30.00); COMMIT; SELECT o.Id, o.CustomerId, c.Name, o.Total FROM dbo.Orders o JOIN dbo.Customers c ON c.Id = o.CustomerId ORDER BY o.Id"
1 | 1 | Maria Garcia | 120.00
2 | 1 | Maria Garcia | 18.25
3 | 2 | David Chen | 45.50
6 | 4 | Kwame Mensah | 30.00
OK (2 row(s) affected) in 0.3 s

PS> .\FixLab.exe 'Server=(localdb)\MSSQLLocalDB;Database=fix7_lab;Integrated Security=true' "BEGIN TRAN; DELETE FROM dbo.Orders WHERE CustomerId = 1; DELETE FROM dbo.Customers WHERE Id = 1; COMMIT; SELECT Id, Name FROM dbo.Customers ORDER BY Id"
2 | David Chen
3 | Aiko Tanaka
4 | Kwame Mensah
OK (3 row(s) affected) in 0.3 s

The first batch inserts the parent and uses its new identity value, from SCOPE_IDENTITY(), for the child. The new order got Id 6 because the two refused inserts had already used 4 and 5. The second batch deletes children before the parent, inside one transaction so a failure leaves both tables as they were.

When orders really belong to their customer, let the database do the second step:

PS> .\FixLab.exe 'Server=(localdb)\MSSQLLocalDB;Database=fix7_lab;Integrated Security=true' "ALTER TABLE dbo.Orders DROP CONSTRAINT FK_Orders_Customers; ALTER TABLE dbo.Orders ADD CONSTRAINT FK_Orders_Customers FOREIGN KEY (CustomerId) REFERENCES dbo.Customers (Id) ON DELETE CASCADE; DELETE FROM dbo.Customers WHERE Id = 2; SELECT Id, CustomerId, Total FROM dbo.Orders ORDER BY Id"
6 | 4 | 30.00
OK (1 row(s) affected) in 0.3 s

Deleting customer 2 removed order 3 with it. Cascade is wrong where the children are records you must keep, such as invoices; there, keep NO ACTION and let error 547 protect them. sys.foreign_keys shows which rule each key has in its delete_referential_action_desc column.

How it was reproduced

A console app from dotnet new console -n FixLab -f net10.0 (.NET SDK 10.0.403) with Microsoft.Data.SqlClient 7.1.1, which runs the SQL in its second argument, against SQL Server 2022 LocalDB 16.0.1210.3. sqlcmd 16 printed the insert case as Msg 547, Level 16, State 1 with the same text.

Frequently asked

How do I find which value breaks a FOREIGN KEY constraint in SQL Server?

The message names the parent table and column. Look for the value you inserted in that column; for a bulk insert, find the child rows whose key has no match with a LEFT JOIN to the parent WHERE parent.Id IS NULL.

How do I delete a row referenced by a foreign key in SQL Server?

Delete the child rows first and then the parent, inside one transaction, or change the foreign key to ON DELETE CASCADE if the children should disappear with their parent.

Is error 547 always a foreign key problem?

No. CHECK constraint failures use the same number 547, with the text conflicted with the CHECK constraint. Read the constraint name in the message to know which rule was broken.

Choosing between cascade, set null and restrict is covered in the one-to-many part of Database Design for Developers. More decoded errors in the Fixes category.