Delete Orphaned Child Rows

Remove child rows that reference missing parent rows before enforcing referential integrity.

Created May 10, 2026 Last updated May 10, 2026 5/5 supported engines validation-green 1 example 2 scenarios

Docker-validated Not currently validation-green

Example 1

Delete orders whose customer does not exist

The delete removes orders 102 and 104 because no customer row matches their customer_id. Orders 101 and 103 remain because they still have valid parent rows. The final SELECT verifies the surviving child rows before a foreign key is added or re-enabled.

MySQL MariaDB SQL Server

Engine-specific syntax

Source table data

Setup

CREATE TABLE customers (id INT, name VARCHAR(50));

CREATE TABLE orders (id INT, customer_id INT, total INT);

INSERT INTO
  customers
VALUES
  (1, 'Ada'),
  (2, 'Bob');

INSERT INTO
  orders
VALUES
  (101, 1, 50),
  (102, 9, 75),
  (103, 2, 20),
  (104, 8, 40);

Validated query

SQL

DELETE o
FROM
  orders o
  LEFT JOIN customers c ON c.id = o.customer_id
WHERE
  c.id IS NULL;

SELECT
  id,
  customer_id,
  total
FROM
  orders
ORDER BY
  id;

Expected result

id	customer_id	total
101	1	50
103	2	20

PostgreSQL

Engine-specific syntax

Source table data

Setup

CREATE TABLE customers (id INT, name VARCHAR(50));

CREATE TABLE orders (id INT, customer_id INT, total INT);

INSERT INTO
  customers
VALUES
  (1, 'Ada'),
  (2, 'Bob');

INSERT INTO
  orders
VALUES
  (101, 1, 50),
  (102, 9, 75),
  (103, 2, 20),
  (104, 8, 40);

Validated query

SQL

DELETE FROM orders o
WHERE
  NOT EXISTS (
    SELECT
      1
    FROM
      customers c
    WHERE
      c.id = o.customer_id
  );

SELECT
  id,
  customer_id,
  total
FROM
  orders
ORDER BY
  id;

Expected result

id	customer_id	total
101	1	50
103	2	20

SQLite

Engine-specific syntax

Source table data

Setup

CREATE TABLE customers (id INT, name VARCHAR(50));

CREATE TABLE orders (id INT, customer_id INT, total INT);

INSERT INTO
  customers
VALUES
  (1, 'Ada'),
  (2, 'Bob');

INSERT INTO
  orders
VALUES
  (101, 1, 50),
  (102, 9, 75),
  (103, 2, 20),
  (104, 8, 40);

Validated query

SQL

DELETE FROM orders
WHERE
  NOT EXISTS (
    SELECT
      1
    FROM
      customers c
    WHERE
      c.id = orders.customer_id
  );

SELECT
  id,
  customer_id,
  total
FROM
  orders
ORDER BY
  id;

Expected result

id	customer_id	total
101	1	50
103	2	20

The cleanup intent is the same, but joined-delete syntax differs by engine.

Useful when

Where this command helps.

cleaning orphaned detail rows before adding a foreign key
removing stale records left behind after parent data was deleted outside constraints

Explanation

What the command is doing.

After auditing orphaned child rows, one cleanup option is to delete the broken child records. Use a parent lookup in the DELETE filter so only rows with no matching parent are removed. This is useful for stale activity, imported detail records, or failed partial migrations, but it should be run only after deciding that the child rows cannot be repaired by creating or mapping the missing parent records.

Delete Orphaned Child Rows

Delete orders whose customer does not exist

Where this command helps.

What the command is doing.

Keep moving through nearby tasks.