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.

foreign_key_check_report.sql 
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 ()), 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 ()), 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;
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.