MySQL Error 1451

Error number

1451

SQLSTATE

23000

Symbol

ER_ROW_IS_REFERENCED_2

Product

MySQL Server

Versions

MySQL 5.7, 8.0 and 8.4 (InnoDB tables with FOREIGN KEY constraints)

Category

Keys & Constraints

Last reviewed

24 October 2026

What it means

Error 1451 means you tried to delete a parent row, or change its key, while rows in a child table still reference it through a foreign key, so MySQL rejected the statement.

A foreign key protects child rows from losing their parent. With the default rule (no ON DELETE or ON UPDATE clause, which InnoDB treats as RESTRICT), deleting a customer who still has orders, or changing that customer's id, is rejected immediately with 1451. Note which table the message names: it is the child table that blocks you (`shop`.`orders`), with the constraint, the child column and the parent table you were changing.

The block can come from further down a chain. If orders cascade from customers but invoices still reference orders, deleting a customer names invoices, not orders. REPLACE also raises 1451, because it deletes the old parent row before inserting the new one. Emptying or dropping a referenced table gives different errors (1701 for TRUNCATE, 3730 for DROP TABLE). The opposite case, a child pointing at a missing parent, is error 1452.

Common causes

• Deleting a parent row (a customer, product or category) that child rows still reference.
• Updating a parent primary key value that child rows use, for example renumbering ids or changing a natural key such as a product code.
• REPLACE INTO on a parent table: REPLACE deletes the existing row and inserts a new one, so the delete step is blocked.
• A cascading delete reaches a table further down whose own foreign key has no ON DELETE CASCADE, so the whole statement is rolled back.
• A cleanup or test-reset script deletes tables in the wrong order (parents before children).

Diagnose it

Read-only These statements only read metadata (information_schema, performance_schema and SHOW statements) or your own data. They do not change data, privileges or configuration, so they are safe to run first.

SELECT rc.constraint_name, rc.table_name AS child_table, rc.referenced_table_name AS parent_table,
       rc.delete_rule, rc.update_rule
FROM information_schema.referential_constraints AS rc
WHERE rc.constraint_schema = DATABASE()
  AND rc.referenced_table_name = 'customers';

SELECT id, customer_id
FROM orders
WHERE customer_id = 1;

SELECT c.id, c.name, COUNT(o.id) AS order_count
FROM customers AS c
LEFT JOIN orders AS o ON o.customer_id = c.id
WHERE c.id IN (1, 2, 3)  -- the parents you plan to delete
GROUP BY c.id, c.name;

How to fix it

Each fix is labelled with what it changes. Try read-only checks and the least invasive fix first, and test changes outside production.

START TRANSACTION;
DELETE FROM orders WHERE customer_id = 1;
DELETE FROM customers WHERE id = 1;
COMMIT;

UPDATE customers SET is_active = 0 WHERE id = 1;

ALTER TABLE orders
  DROP FOREIGN KEY fk_orders_customer,
  ADD CONSTRAINT fk_orders_customer FOREIGN KEY (customer_id)
      REFERENCES customers (id) ON DELETE CASCADE;

INSERT INTO customers (id, name)
VALUES (1, 'Sara B')
AS new
ON DUPLICATE KEY UPDATE name = new.name;

Examples

Deleting a customer who still has orders

CREATE TABLE customers (
    id   INT PRIMARY KEY,
    name VARCHAR(100)
);
CREATE TABLE orders (
    id          INT PRIMARY KEY,
    customer_id INT NOT NULL,
    CONSTRAINT fk_orders_customer FOREIGN KEY (customer_id) REFERENCES customers (id)
);
INSERT INTO customers VALUES (1, 'Sara');
INSERT INTO orders VALUES (100, 1);

DELETE FROM customers WHERE id = 1;

ERROR 1451 (23000): Cannot delete or update a parent row: a foreign key constraint fails (`shop`.`orders`, CONSTRAINT `fk_orders_customer` FOREIGN KEY (`customer_id`) REFERENCES `customers` (`id`))

Changing a parent key that child rows use

CREATE TABLE customers (id INT PRIMARY KEY, name VARCHAR(100));
CREATE TABLE orders (
    id          INT PRIMARY KEY,
    customer_id INT NOT NULL,
    CONSTRAINT fk_orders_customer FOREIGN KEY (customer_id) REFERENCES customers (id)
);
INSERT INTO customers VALUES (1, 'Sara');
INSERT INTO orders VALUES (100, 1);

UPDATE customers SET id = 2 WHERE id = 1;

ERROR 1451 (23000): Cannot delete or update a parent row: a foreign key constraint fails (`shop`.`orders`, CONSTRAINT `fk_orders_customer` FOREIGN KEY (`customer_id`) REFERENCES `customers` (`id`))

REPLACE INTO on a referenced parent row

CREATE TABLE customers (id INT PRIMARY KEY, name VARCHAR(100));
CREATE TABLE orders (
    id          INT PRIMARY KEY,
    customer_id INT NOT NULL,
    CONSTRAINT fk_orders_customer FOREIGN KEY (customer_id) REFERENCES customers (id)
);
INSERT INTO customers VALUES (1, 'Sara');
INSERT INTO orders VALUES (100, 1);

REPLACE INTO customers VALUES (1, 'Sara B');  -- REPLACE deletes the old row first

ERROR 1451 (23000): Cannot delete or update a parent row: a foreign key constraint fails (`shop`.`orders`, CONSTRAINT `fk_orders_customer` FOREIGN KEY (`customer_id`) REFERENCES `customers` (`id`))

A cascade blocked further down the chain

CREATE TABLE customers (id INT PRIMARY KEY, name VARCHAR(100));
CREATE TABLE orders (
    id          INT PRIMARY KEY,
    customer_id INT NOT NULL,
    CONSTRAINT fk_orders_customer FOREIGN KEY (customer_id) REFERENCES customers (id) ON DELETE CASCADE
);
CREATE TABLE invoices (
    id       INT PRIMARY KEY,
    order_id INT NOT NULL,
    CONSTRAINT fk_invoices_order FOREIGN KEY (order_id) REFERENCES orders (id)  -- no CASCADE here
);
INSERT INTO customers VALUES (1, 'Sara');
INSERT INTO orders VALUES (100, 1);
INSERT INTO invoices VALUES (5000, 100);

DELETE FROM customers WHERE id = 1;

ERROR 1451 (23000): Cannot delete or update a parent row: a foreign key constraint fails (`shop`.`invoices`, CONSTRAINT `fk_invoices_order` FOREIGN KEY (`order_id`) REFERENCES `orders` (`id`))

How to prevent it

Decide the delete rule for every relationship when you design it: RESTRICT (the default) for records with history, CASCADE only for rows that have no meaning without their parent (such as order lines), and SET NULL for optional links. Prefer soft deletes for customers, products and accounts.

Keep primary keys stable and use upserts instead of REPLACE on parent tables. In cleanup and test scripts, delete children before parents. Handle error 1451 in application code so the user sees "this customer still has orders" rather than a database error.

Common questions

Learn more on CodeWithSQL

• Primary key vs foreign key
• SQL constraints explained

Official documentation

• MySQL 8.0 server error message reference
• FOREIGN KEY constraints (referential actions)
• information_schema REFERENTIAL_CONSTRAINTS table
• REPLACE statement

Last technically reviewed 24 October 2026; examples and diagnostics run on MySQL 8.0.46. Message templates checked against the server's error catalogue (perror) for that version.