SQL Server Error 547: Foreign Key Constraint Conflict (Fix Guide)

The Error Message

What Causes SQL Server 547?

SQL Server Error 547 fires when an INSERT, UPDATE, or DELETE violates a foreign key constraint. You are trying to reference a parent row that does not exist, or delete a parent row that still has child records.

Common Causes

How to Fix It

Step 1: Check the referenced value exists

The foreign key value must exist in the parent table before you can insert a child record.

-- Find which constraint failed:
-- Error message includes the constraint name

-- Check the parent table:
SELECT * FROM departments
WHERE id = 999;  -- the value you're trying to reference

-- Insert the parent first:
INSERT INTO departments (id, name) VALUES (999, 'Engineering');

Step 2: Delete in correct order

Delete child records before parent records to avoid constraint violations.

-- Wrong: delete parent first
DELETE FROM departments WHERE id = 5;

-- Correct: delete children first, then parent
DELETE FROM employees WHERE department_id = 5;
DELETE FROM departments WHERE id = 5;

Step 3: Add CASCADE to foreign key

CASCADE automatically deletes or updates child records when the parent changes.

-- Drop and recreate with CASCADE:
ALTER TABLE employees
DROP CONSTRAINT FK_employees_department;

ALTER TABLE employees
ADD CONSTRAINT FK_employees_department
FOREIGN KEY (department_id)
REFERENCES departments(id)
ON DELETE CASCADE
ON UPDATE CASCADE;

Step 4: Find orphan records

Use LEFT JOIN to find orphaned child records that reference non-existent parents.

-- Find child records without parents:
SELECT e.*
FROM employees e
LEFT JOIN departments d ON e.department_id = d.id
WHERE d.id IS NULL;

How to Prevent This Error

Use CASCADE on foreign keys when appropriate. Always insert parent records before children. Use transactions for related inserts to ensure all-or-nothing execution.

Fix SQL Server Errors with AI2SQL

Instead of debugging SQL syntax manually, describe what you need in plain English and let AI2SQL generate the correct query for SQL Server.

Frequently Asked Questions