SQL Server Error 547

Why it happens

Error 547 means a statement would leave your data in a state that a constraint forbids, so SQL Server rejects the whole statement and rolls it back. The message spells out which constraint blocked you, the database, the table, and the column involved, so read it carefully before changing anything.

There are three distinct flavours behind the same error number. A FOREIGN KEY conflict on an INSERT or UPDATE means you referenced a parent key value that does not exist yet in the parent table. A REFERENCE conflict on a DELETE or UPDATE means child rows still point at the row you are trying to remove or change. A CHECK conflict means a column value breaks a rule the CHECK constraint enforces.

The error is severity 16, a normal catchable constraint violation rather than a server problem. The fix depends entirely on which flavour you hit, so the first step is always to read whether the message names a FOREIGN KEY, a REFERENCE, or a CHECK constraint.

Examples

FOREIGN KEY conflict on INSERT (parent does not exist)

CREATE TABLE dbo.Customers (
    id   INT PRIMARY KEY,
    name NVARCHAR(100)
);

CREATE TABLE dbo.Orders (
    id          INT PRIMARY KEY,
    customer_id INT CONSTRAINT FK_Orders_Customers
                    REFERENCES dbo.Customers (id),
    total       DECIMAL(10,2)
);

-- No customer with id 99 exists yet
INSERT INTO dbo.Orders (id, customer_id, total) VALUES (1, 99, 50.00);

Msg 547, Level 16, State 0, Line 14
The INSERT statement conflicted with the FOREIGN KEY constraint "FK_Orders_Customers". The conflict occurred in database "Shop", table "dbo.Customers", column 'id'.

DELETE blocked by child rows (REFERENCE constraint)

-- Customer 1 still has orders that reference it
DELETE FROM dbo.Customers WHERE id = 1;

Msg 547, Level 16, State 0, Line 2
The DELETE statement conflicted with the REFERENCE constraint "FK_Orders_Customers". The conflict occurred in database "Shop", table "dbo.Orders", column 'customer_id'.

CHECK constraint conflict (value breaks a rule)

ALTER TABLE dbo.Orders
    ADD CONSTRAINT CK_Orders_Total CHECK (total >= 0);

-- A negative total violates the rule
INSERT INTO dbo.Orders (id, customer_id, total) VALUES (2, 1, -5.00);

Msg 547, Level 16, State 0, Line 5
The INSERT statement conflicted with the CHECK constraint "CK_Orders_Total". The conflict occurred in database "Shop", table "dbo.Orders", column 'total'.

How to fix it

-- Create the parent first
INSERT INTO dbo.Customers (id, name) VALUES (99, 'Sara');

-- Now the child insert succeeds
INSERT INTO dbo.Orders (id, customer_id, total) VALUES (1, 99, 50.00);

-- Option A: clear the children first, then the parent
DELETE FROM dbo.Orders    WHERE customer_id = 1;
DELETE FROM dbo.Customers WHERE id = 1;

-- Option B: define the FK to cascade deletes
ALTER TABLE dbo.Orders
    ADD CONSTRAINT FK_Orders_Customers
    FOREIGN KEY (customer_id) REFERENCES dbo.Customers (id)
    ON DELETE CASCADE;

-- See what the rule requires
SELECT definition
FROM sys.check_constraints
WHERE name = 'CK_Orders_Total';

-- Supply a value that satisfies total >= 0
INSERT INTO dbo.Orders (id, customer_id, total) VALUES (2, 1, 5.00);

ALTER TABLE dbo.Orders NOCHECK CONSTRAINT FK_Orders_Customers;

-- ... load and clean data so every customer_id is valid ...

-- Re-enable and revalidate existing rows
ALTER TABLE dbo.Orders WITH CHECK CHECK CONSTRAINT FK_Orders_Customers;

How to prevent it

Seed your reference and lookup tables before loading data that depends on them, and enforce a consistent insert order so parents always exist before their children. This removes the most common FOREIGN KEY cause of 547 outright.

Validate values against your CHECK rules in the application before submitting, and decide up front whether related deletes should cascade or be blocked. For the full picture of how FOREIGN KEY, REFERENCE, and CHECK constraints work together, see the constraints guide and primary key vs foreign key.

Common questions