Pragmatic SQLite Administration

Foreign-Key Check

Report Orphan Rows

pragma_foreign_key_check can report rows that violate declared foreign-key relationships.

Program

Play the script to choose a minimum order total and inspect orphaned orders above that threshold.

min_total
foreign_key_check_report.sql
Replay: real traced execution (multi-file project)
PRAGMA foreign_keys = OFF;
CREATE TABLE customers (id INTEGER PRIMARY KEY, name TEXT);
CREATE TABLE orders (id INTEGER PRIMARY KEY, customer_id INTEGER REFERENCES customers(id), total INTEGER);
INSERT INTO customers VALUES (1, 'Ada'), (2, 'Lin');
INSERT INTO orders VALUES (10, 1, 40), (11, 3, 70), (12, 2, 20), (13, 99, 10);
WITH params(min_total) AS (VALUES (0)), violations AS (SELECT rowid FROM pragma_foreign_key_check('orders')), orphan_orders AS (SELECT orders.id AS order_id, orders.customer_id, orders.total FROM violations JOIN orders ON orders.rowid = violations.rowid WHERE orders.total >= (SELECT min_total FROM params)) SELECT order_id, customer_id, total FROM orphan_orders ORDER BY order_id;

PRAGMA foreign_keys = OFF;
CREATE TABLE customers (id INTEGER PRIMARY KEY, name TEXT);
CREATE TABLE orders (id INTEGER PRIMARY KEY, customer_id INTEGER REFERENCES customers(id), total INTEGER);
INSERT INTO customers VALUES (1, 'Ada'), (2, 'Lin');
INSERT INTO orders VALUES (10, 1, 40), (11, 3, 70), (12, 2, 20), (13, 99, 10);
WITH params(min_total) AS (VALUES (50)), violations AS (SELECT rowid FROM pragma_foreign_key_check('orders')), orphan_orders AS (SELECT orders.id AS order_id, orders.customer_id, orders.total FROM violations JOIN orders ON orders.rowid = violations.rowid WHERE orders.total >= (SELECT min_total FROM params)) SELECT order_id, customer_id, total FROM orphan_orders ORDER BY order_id;

  1. settings ← 1 row

    1PRAGMA foreign_keys = OFF;2CREATE TABLE customers (id INTEGER PRIMARY KEY, name TEXT);
    values this step1 rowsettings

  2. tables ← 1 row

    1PRAGMA foreign_keys = OFF;2CREATE TABLE customers (id INTEGER PRIMARY KEY, name TEXT);3CREATE TABLE orders (id INTEGER PRIMARY KEY, customer_id INTEGER REFERENCES customers(id), total INTEGER);
    values this step1 rowtables

  3. tables ← 2 rows

    2CREATE TABLE customers (id INTEGER PRIMARY KEY, name TEXT);3CREATE TABLE orders (id INTEGER PRIMARY KEY, customer_id INTEGER REFERENCES customers(id), total INTEGER);4INSERT INTO customers VALUES (1, 'Ada'), (2, 'Lin');
    values this step2 rowstables

  4. customers ← 2 rows

    3CREATE TABLE orders (id INTEGER PRIMARY KEY, customer_id INTEGER REFERENCES customers(id), total INTEGER);4INSERT INTO customers VALUES (1, 'Ada'), (2, 'Lin');5INSERT INTO orders VALUES (10, 1, 40), (11, 3, 70), (12, 2, 20), (13, 99, 10);
    values this step2 rowscustomers

  5. orders ← 4 rows

    4INSERT INTO customers VALUES (1, 'Ada'), (2, 'Lin');5INSERT INTO orders VALUES (10, 1, 40), (11, 3, 70), (12, 2, 20), (13, 99, 10);6WITH params(min_total) AS (VALUES (0)), violations AS (SELECT rowid FROM pragma_foreign_key_check('orders')), orphan_orders AS (SELECT orders.id AS order_id, orders.customer_id, orders.total FROM violations JOIN orders ON orders.rowid = violations.rowid WHERE orders.total >= (SELECT min_total FROM params)) SELECT order_id, customer_id, total FROM orphan_orders ORDER BY order_id;
    values this step4 rowsorders

  6. result ← 2 rows

    5INSERT INTO orders VALUES (10, 1, 40), (11, 3, 70), (12, 2, 20), (13, 99, 10);6WITH params(min_total) AS (VALUES (0)), violations AS (SELECT rowid FROM pragma_foreign_key_check('orders')), orphan_orders AS (SELECT orders.id AS order_id, orders.customer_id, orders.total FROM violations JOIN orders ON orders.rowid = violations.rowid WHERE orders.total >= (SELECT min_total FROM params)) SELECT order_id, customer_id, total FROM orphan_orders ORDER BY order_id;
    values this step2 rowsresult

  1. settings ← 1 row

    1PRAGMA foreign_keys = OFF;2CREATE TABLE customers (id INTEGER PRIMARY KEY, name TEXT);
    values this step1 rowsettings

  2. tables ← 1 row

    1PRAGMA foreign_keys = OFF;2CREATE TABLE customers (id INTEGER PRIMARY KEY, name TEXT);3CREATE TABLE orders (id INTEGER PRIMARY KEY, customer_id INTEGER REFERENCES customers(id), total INTEGER);
    values this step1 rowtables

  3. tables ← 2 rows

    2CREATE TABLE customers (id INTEGER PRIMARY KEY, name TEXT);3CREATE TABLE orders (id INTEGER PRIMARY KEY, customer_id INTEGER REFERENCES customers(id), total INTEGER);4INSERT INTO customers VALUES (1, 'Ada'), (2, 'Lin');
    values this step2 rowstables

  4. customers ← 2 rows

    3CREATE TABLE orders (id INTEGER PRIMARY KEY, customer_id INTEGER REFERENCES customers(id), total INTEGER);4INSERT INTO customers VALUES (1, 'Ada'), (2, 'Lin');5INSERT INTO orders VALUES (10, 1, 40), (11, 3, 70), (12, 2, 20), (13, 99, 10);
    values this step2 rowscustomers

  5. orders ← 4 rows

    4INSERT INTO customers VALUES (1, 'Ada'), (2, 'Lin');5INSERT INTO orders VALUES (10, 1, 40), (11, 3, 70), (12, 2, 20), (13, 99, 10);6WITH params(min_total) AS (VALUES (50)), violations AS (SELECT rowid FROM pragma_foreign_key_check('orders')), orphan_orders AS (SELECT orders.id AS order_id, orders.customer_id, orders.total FROM violations JOIN orders ON orders.rowid = violations.rowid WHERE orders.total >= (SELECT min_total FROM params)) SELECT order_id, customer_id, total FROM orphan_orders ORDER BY order_id;
    values this step4 rowsorders

  6. result ← 1 row

    5INSERT INTO orders VALUES (10, 1, 40), (11, 3, 70), (12, 2, 20), (13, 99, 10);6WITH params(min_total) AS (VALUES (50)), violations AS (SELECT rowid FROM pragma_foreign_key_check('orders')), orphan_orders AS (SELECT orders.id AS order_id, orders.customer_id, orders.total FROM violations JOIN orders ON orders.rowid = violations.rowid WHERE orders.total >= (SELECT min_total FROM params)) SELECT order_id, customer_id, total FROM orphan_orders ORDER BY order_id;
    values this step1 rowresult
foreign key check `pragma_foreign_key_check` reports child rows whose parent key is missing.
orphan rows Joining the pragma output back to the table shows the offending business values.
threshold selector `min_total` focuses the report without changing the relationship check.