The INSERT statement conflicted with the FOREIGN KEY constraint

What it means

A foreign key ties a column in one table (the child, books.author_id) to the primary key or a unique key of another (the parent, authors.id): every non-NULL value in the child must exist in the parent. SQL Server checks this on every write and stops the statement with error 547 if it would break the rule. Nothing the statement did is kept.

The wording tells you which side failed, and the table it names is the other table:

Error 547 is also what a CHECK constraint raises; the constraint type in the message tells them apart.

Common causes

1. The parent row doesn’t exist: a wrong or stale id, a parent deleted earlier, or a parent inserted by another transaction that hasn’t committed (your insert waits for it, and fails if it rolls back).
2. Deleting a parent that still has children, such as an author with books, a customer with orders.
3. Loading tables in the wrong order: an import or seed script that fills books before authors.
4. A placeholder instead of NULL. Code that sends 0 or '' for “no parent”. NULL passes a foreign key check; 0 must exist in the parent.
5. A cascade that stops partway. ON DELETE CASCADE from authors to books removes the books, but if another table references books without a cascade, the whole delete fails with a REFERENCE conflict on that table.
6. Adding a foreign key to existing data that has orphans: rows pointing at parents that are gone.

How to fix it

Find the missing parents

List child rows whose parent doesn’t exist:

SELECT b.id, b.author_id
FROM dbo.books AS b
WHERE b.author_id IS NOT NULL
  AND NOT EXISTS (SELECT 1 FROM dbo.authors AS a WHERE a.id = b.author_id);

Insert the missing parents, correct the ids, or delete the orphans; then add or re-check the constraint.

Find what still references a row

To see which tables point at authors, and what each does on delete:

SELECT fk.name,
       OBJECT_SCHEMA_NAME(fk.parent_object_id) + '.' + OBJECT_NAME(fk.parent_object_id) AS referencing_table,
       fk.delete_referential_action_desc
FROM sys.foreign_keys AS fk
WHERE fk.referenced_object_id = OBJECT_ID('dbo.authors');

Then delete (or re-point) the child rows first, in one transaction:

BEGIN TRAN;
DELETE FROM dbo.reviews WHERE book_id IN (SELECT id FROM dbo.books WHERE author_id = 1);
DELETE FROM dbo.books WHERE author_id = 1;
DELETE FROM dbo.authors WHERE id = 1;
COMMIT;

Let the database cascade

If child rows should always go with their parent, recreate the foreign key with a delete rule (CASCADE, SET NULL or SET DEFAULT):

ALTER TABLE dbo.books DROP CONSTRAINT FK_books_authors;
ALTER TABLE dbo.books ADD CONSTRAINT FK_books_authors
  FOREIGN KEY (author_id) REFERENCES dbo.authors (id) ON DELETE CASCADE;

Every table further down the chain needs its own rule too, or the cascade fails there.

Load parents first, or check after loading

Insert parent tables before child tables. If you can’t, disable the foreign key for the load and check it afterwards:

ALTER TABLE dbo.books NOCHECK CONSTRAINT FK_books_authors;
-- load the data
ALTER TABLE dbo.books WITH CHECK CHECK CONSTRAINT FK_books_authors;

The second statement fails with 547 if the load left orphans, and tells you to clean them up. Write WITH CHECK CHECK (the doubled word is right): without WITH CHECK, SQL Server re-enables the constraint without checking existing rows, and marks it as not trusted (sys.foreign_keys.is_not_trusted = 1), so the optimiser can’t rely on it. The same goes for adding a constraint WITH NOCHECK.

Send NULL for “no parent”

Make the column nullable and send NULL rather than 0 or an empty string when a row has no parent.

Reproduce it

On SQL Server 2022 (16.0.4295.3) with sqlcmd, in a scratch schema: authors (id 1), and books with author_id … REFERENCES authors (id) and one book by author 1.

INSERT INTO seo_sqlerr_data.books (id, author_id, title) VALUES (11, 2, N'Kindred');
DELETE FROM seo_sqlerr_data.authors WHERE id = 1;

Msg 547, Level 16, State 1, Server 7732422b7f56, Line 1
The INSERT statement conflicted with the FOREIGN KEY constraint "FK_books_authors". The conflict occurred in database "inlet", table "seo_sqlerr_data.authors", column 'id'.
The statement has been terminated.
Msg 547, Level 16, State 1, Server 7732422b7f56, Line 1
The DELETE statement conflicted with the REFERENCE constraint "FK_books_authors". The conflict occurred in database "inlet", table "seo_sqlerr_data.books", column 'author_id'.
The statement has been terminated.

Changing the book’s author_id to 3, and the author’s id to 5, gave the two UPDATE forms. Adding a foreign key from reviews to books while one review pointed at book 99:

Msg 547, Level 16, State 1, Server 7732422b7f56, Line 1
The ALTER TABLE statement conflicted with the FOREIGN KEY constraint "FK_reviews_books". The conflict occurred in database "inlet", table "seo_sqlerr_data.books", column 'id'.

Added WITH NOCHECK, the same constraint went in with is_not_trusted = 1; after deleting the orphan, WITH CHECK CHECK CONSTRAINT set it back to 0. With ON DELETE CASCADE from authors to books, deleting author 1 still failed, because a review referenced the author’s book:

Msg 547, Level 16, State 1, Server 7732422b7f56, Line 2
The DELETE statement conflicted with the REFERENCE constraint "FK_reviews_books". The conflict occurred in database "inlet", table "seo_sqlerr_data.reviews", column 'book_id'.
The statement has been terminated.

A NULL book_id was accepted and a 0 was refused. A book inserted for author 7 while another session’s insert of author 7 was still uncommitted waited two seconds for it, then failed with the INSERT form above when that session rolled back. TRUNCATE TABLE on a referenced table fails with a different error, 4712 (Cannot truncate table … because it is being referenced by a FOREIGN KEY constraint.), even when the referencing table is empty. SQL Server 2019 and 2025 printed the same messages.

In Inlet

Grid edits are staged until you commit (⌘S), and Review shows what will run first, so you can check the order: parents inserted before children, children deleted before parents. When SQL Server refuses, Inlet shows its message with SQL Server error 547, state 1, severity 16 and links to this page. A table’s definition shows its CREATE TABLE with constraints, so you can see where a foreign key points; the structure designer writes the T-SQL when you change one.