FOREIGN KEY in SQL: How Referential Integrity Works

An order exists in your orders table referencing a customer_id that no longer exists in customers. A payment record points to an order_id that was deleted. These are orphaned rows and they silently corrupt joins, inflate counts, and produce wrong aggregates. Foreign keys are the mechanism that prevents this class of problem from ever reaching your data.

What a FOREIGN KEY Does

A foreign key constraint tells the database that a column in one table must always match a value in a specific column of another table. The table containing the foreign key is called the child table. The table being referenced is the parent table.

-- orders references customers via customer_id
CREATE TABLE orders (
    order_id         INTEGER PRIMARY KEY,
    customer_id      INTEGER NOT NULL,
    employee_id      INTEGER,
    order_date       TEXT    NOT NULL,
    status           TEXT    NOT NULL,
    total_amount     REAL    NOT NULL,
    FOREIGN KEY (customer_id) REFERENCES customers (customer_id),
    FOREIGN KEY (employee_id) REFERENCES employees (employee_id)
);

With this constraint active, inserting an order with a customer_id that does not exist in customers raises an error immediately. The bad data never reaches the table.

-- This would fail with foreign key enforcement enabled
-- INSERT INTO orders (order_id, customer_id, order_date, status, total_amount)
-- VALUES (9999, 99999, '2024-06-01', 'Pending', 150.00);
-- customer_id 99999 does not exist in customers

The Full Foreign Key Map of the Ecommerce Schema

Understanding which columns link to which tables helps you write joins correctly and know where orphaned data could hide.

| Child table | Foreign key column | Parent table | Referenced column | |-------------|-------------------|--------------|------------------| | products | category_id | categories | category_id | | orders | customer_id | customers | customer_id | | orders | employee_id | employees | employee_id | | order_items | order_id | orders | order_id | | order_items | product_id | products | product_id | | payments | order_id | orders | order_id | | employees | manager_id | employees | employee_id |

The last row is a self-referencing foreign key. manager_id in employees points back to employee_id in the same table, modelling the reporting hierarchy.

-- Follow the self-referencing key to find each employee's manager
SELECT
    e.employee_id,
    e.first_name    AS employee_name,
    e.role,
    m.first_name    AS manager_name,
    m.role          AS manager_role
FROM employees e
LEFT JOIN employees m ON e.manager_id = m.employee_id
ORDER BY e.employee_id;

Enabling Foreign Keys in SQLite

SQLite parses foreign key declarations but does not enforce them unless you explicitly turn enforcement on. Run this at the start of your session before any other statements.

PRAGMA foreign_keys = ON;

Without this, you can insert rows with invalid foreign key values and SQLite will not complain. This is a common source of silent data quality problems in SQLite-backed applications.

-- Confirm foreign key enforcement is active
PRAGMA foreign_keys;
-- Returns 1 if ON, 0 if OFF

ON DELETE and ON UPDATE Actions

When a parent row is deleted or its primary key is updated, the database needs to know what to do with the child rows that reference it. You specify this with ON DELETE and ON UPDATE clauses.

| Action | Behaviour | |--------|-----------| | RESTRICT | Block the delete or update if child rows exist (default) | | CASCADE | Delete or update child rows automatically | | SET NULL | Set the foreign key column to NULL in child rows | | SET DEFAULT | Set the foreign key column to its default value | | NO ACTION | Same as RESTRICT in most databases |

-- order_items cascade-deletes when the parent order is deleted
CREATE TABLE order_items (
    item_id     INTEGER PRIMARY KEY,
    order_id    INTEGER NOT NULL,
    product_id  INTEGER NOT NULL,
    quantity    INTEGER NOT NULL,
    unit_price  REAL    NOT NULL,
    line_total  REAL    NOT NULL,
    FOREIGN KEY (order_id)   REFERENCES orders   (order_id)   ON DELETE CASCADE,
    FOREIGN KEY (product_id) REFERENCES products (product_id) ON DELETE RESTRICT
);

Here deleting an order also deletes its line items automatically. But you cannot delete a product that still has order items referencing it.

Detecting Orphaned Rows Without Constraint Enforcement

If you are working with a database where foreign key enforcement was off, you may already have orphaned rows. Use a LEFT JOIN with a NULL check to find them.

-- Find order_items that reference an order_id not in orders
SELECT oi.item_id, oi.order_id, oi.product_id, oi.line_total
FROM order_items oi
LEFT JOIN orders o ON oi.order_id = o.order_id
WHERE o.order_id IS NULL;

-- Find orders referencing a customer_id not in customers
SELECT o.order_id, o.customer_id, o.order_date, o.total_amount
FROM orders o
LEFT JOIN customers c ON o.customer_id = c.customer_id
WHERE c.customer_id IS NULL;

-- Find payments referencing an order_id not in orders
SELECT pay.payment_id, pay.order_id, pay.amount, pay.status
FROM payments pay
LEFT JOIN orders o ON pay.order_id = o.order_id
WHERE o.order_id IS NULL;

If any of these return rows, you have referential integrity violations that need to be cleaned up before adding foreign key constraints.

Common Mistakes

-- wrong
SELECT o.order_id, c.first_name
FROM orders o
JOIN customers c ON o.customer_id = c.customer_id
WHERE o.customer_id = NULL;

-- correct
SELECT o.order_id, o.customer_id
FROM orders o
LEFT JOIN customers c ON o.customer_id = c.customer_id
WHERE c.customer_id IS NULL;

Using = NULL never matches anything. To find orders with no matching customer, use a LEFT JOIN and check for NULL in the parent table's primary key column.

---

-- wrong
PRAGMA foreign_keys = ON;

DELETE FROM orders WHERE order_id = 101;
-- Expects this to also remove order_items rows

-- correct
-- Only works if the foreign key was declared with ON DELETE CASCADE
-- Verify the schema includes: FOREIGN KEY (order_id) REFERENCES orders (order_id) ON DELETE CASCADE
PRAGMA foreign_keys = ON;

DELETE FROM orders WHERE order_id = 101;

CASCADE deletion only works if it was declared in the schema. Enabling PRAGMA foreign_keys activates enforcement of whatever actions were defined, but it does not add cascade behaviour retroactively.

---

-- wrong
SELECT p.product_name, cat.category_name
FROM products p
JOIN categories cat ON p.product_id = cat.category_id;

-- correct
SELECT p.product_name, cat.category_name
FROM products p
JOIN categories cat ON p.category_id = cat.category_id;

Joining on the wrong columns produces a cross-product of mismatched rows with no error message. Always join the foreign key column in the child table to the primary key column in the parent table.

Quick Recap

• A foreign key links a column in a child table to the primary key of a parent table, ensuring every referenced value actually exists.
• SQLite does not enforce foreign keys by default. Run PRAGMA foreign_keys = ON at the start of your session to activate enforcement.
• Use a LEFT JOIN with a WHERE parent_id IS NULL check to detect orphaned rows in tables where referential integrity was not enforced.

SELECT o.order_id, o.order_date, o.total_amount, c.first_name, c.last_name FROM orders o JOIN customers c ON o.customer_id = c.customer_id ORDER BY o.order_date DESC LIMIT 10;

SELECT oi.item_id, oi.order_id, oi.product_id FROM order_items oi LEFT JOIN products p ON oi.product_id = p.product_id WHERE p.product_id IS NULL ORDER BY oi.item_id ASC;

SELECT cat.category_name, COUNT(DISTINCT p.product_id) AS product_count, ROUND(COALESCE(SUM(oi.line_total), 0), 2) AS total_revenue FROM categories cat LEFT JOIN products p ON cat.category_id = p.category_id LEFT JOIN order_items oi ON p.product_id = oi.product_id GROUP BY cat.category_id, cat.category_name ORDER BY total_revenue DESC;