Using Foreign Keys and Referential Integrity Correctly

1. What referential integrity actually means

Referential integrity means that every value in a foreign key column is either NULL or actually points to an existing row in the referenced table. An order with customer_id = 42 may only exist if the customers table actually contains a customer with ID 42. Without this guarantee, so-called orphaned records appear, rows that point to parent records that no longer exist, with consequences that only surface at the next query or application access.

A foreign key, defined in the SQL standard as a FOREIGN KEY constraint, is the mechanism the database itself uses to enforce this guarantee, without application code having to manually safeguard every single insert, update or delete operation. This enforcement at the database level is decisive, because application code can contain bugs, several applications may access the same database concurrently, and manual database changes, for example through a script or an administrator, bypass application logic entirely. Only a constraint at the database level protects consistently in all of these cases at once.

2. FOREIGN KEY syntax fundamentals

The basic syntax of a foreign key consists of three parts: the column in the referencing table, the REFERENCES keyword, and the target table with its referenced column, which almost always has to be the primary key or at least a UNIQUE column. A foreign key can be declared inline with the column definition or as a separate CONSTRAINT clause at the end of the CREATE TABLE statement, which is clearer especially for composite foreign keys and gives the named constraint its own name that can be referenced later.

An often overlooked aspect of foreign key syntax is that the referenced column must have a unique index, either as a PRIMARY KEY or as a UNIQUE constraint. Without this uniqueness, a foreign key value could ambiguously point to multiple rows, which would undermine the entire guarantee of referential integrity. Most database engines therefore refuse to create a foreign key on a column without a matching unique index in the first place, raising an error.

-- Basic FOREIGN KEY syntax: inline and as a named constraint
CREATE TABLE customers (
    customer_id  INT PRIMARY KEY AUTO_INCREMENT,
    full_name    VARCHAR(150) NOT NULL
);

-- Inline foreign key declaration
CREATE TABLE orders_inline (
    order_id     INT PRIMARY KEY AUTO_INCREMENT,
    customer_id  INT NOT NULL REFERENCES customers(customer_id),
    order_date   DATE NOT NULL
);

-- Named constraint, easier to reference later (e.g. to drop or alter it)
CREATE TABLE orders_named (
    order_id     INT PRIMARY KEY AUTO_INCREMENT,
    customer_id  INT NOT NULL,
    order_date   DATE NOT NULL,
    CONSTRAINT fk_orders_customer
        FOREIGN KEY (customer_id) REFERENCES customers(customer_id)
);

-- Adding a foreign key to an existing table
ALTER TABLE orders_named
    ADD CONSTRAINT fk_orders_customer_v2
    FOREIGN KEY (customer_id) REFERENCES customers(customer_id);

3. ON DELETE CASCADE: controlled cascading deletes

ON DELETE CASCADE tells the database to automatically delete every row that references a referenced row via a foreign key when that row gets deleted. The classic example: deleting an order should automatically delete all of its order lines too, because an order line makes no business sense without its order. CASCADE is therefore the right choice for true ownership relationships, where the dependent row cannot exist in business terms without the parent row.

But CASCADE is also the most dangerous option when used carelessly. A CASCADE on a relationship that is not a true ownership relationship, for example when deleting a customer accidentally deletes their entire order history, destroys data that should be preserved for accounting or legal reasons. The rule of thumb for referential integrity with CASCADE: only use it when the dependent entity is truly part of the parent entity and has no standalone business meaning without it, never for relationships between independently existing entities such as customer and order.

-- ON DELETE CASCADE: order lines are true children of the order
CREATE TABLE orders (
    order_id    INT PRIMARY KEY AUTO_INCREMENT,
    customer_id INT NOT NULL,
    order_date  DATE NOT NULL,
    FOREIGN KEY (customer_id) REFERENCES customers(customer_id)
    -- No CASCADE here: deleting a customer should not silently
    -- delete their entire order history
);

CREATE TABLE order_lines (
    order_id    INT NOT NULL,
    product_id  INT NOT NULL,
    quantity    INT NOT NULL,
    PRIMARY KEY (order_id, product_id),
    FOREIGN KEY (order_id) REFERENCES orders(order_id)
        ON DELETE CASCADE  -- a line has no meaning without its order
);

-- Deleting an order automatically removes its lines, nothing else
DELETE FROM orders WHERE order_id = 1001;

4. ON DELETE RESTRICT and NO ACTION

ON DELETE RESTRICT, or equivalently NO ACTION in the SQL standard, prevents deleting a row as long as at least one row still references it via a foreign key. The delete attempt fails with a constraint error, and the referencing row stays unchanged. This is the default behavior in most database engines when no explicit ON DELETE clause is given, and it is the safest option, because it never silently changes or deletes data.

RESTRICT works particularly well for relationships where an accidental delete would have catastrophic consequences, for example deleting a product that is still referenced by open orders: FOREIGN KEY (product_id) REFERENCES products(product_id) ON DELETE RESTRICT makes DELETE FROM products WHERE product_id = 7 fail with a clear constraint error the moment order_items still references that value, instead of letting the database become silently inconsistent. The failure forces the calling code or administrator to handle the situation deliberately, for example by first archiving or reassigning the dependent rows, instead of a delete operation triggering cascading effects across the whole database unnoticed. For referential integrity around critical core entities, RESTRICT is therefore often the more conservative and hence safer default.

5. ON DELETE SET NULL and SET DEFAULT

ON DELETE SET NULL sets the foreign key column to NULL once the referenced row gets deleted, instead of deleting the referencing row itself or preventing the delete. This suits optional relationships, where the referencing row still makes sense to keep around without the reference, for example an employee whose assigned manager got deleted: the employee record stays, but now has no manager assigned for the time being. A strict prerequisite is that the foreign key column allows NULL, otherwise the constraint fails right at creation time.

ON DELETE SET DEFAULT works analogously, but sets the column to a predefined default value instead of NULL, for example an "unknown" category ID instead of a deleted category. This option is used less often than SET NULL, because it requires a sensible default value to actually exist and stay permanently present in the target table. For referential integrity, both variants apply the same principle: they are the right choice for optional, non-owning relationships, while CASCADE should be reserved for true ownership relationships and RESTRICT for protected core entities.

6. Using ON UPDATE behavior correctly

ON UPDATE follows the same options as ON DELETE, CASCADE, RESTRICT, SET NULL and SET DEFAULT, but triggers when the value of the referenced column changes, instead of when the row gets deleted. In a schema with surrogate primary keys, which by definition never change, ON UPDATE CASCADE is mostly irrelevant, because an auto-increment value or a UUID never gets updated anyway. If a natural key is used as the primary key instead, such as a SKU, ON UPDATE CASCADE becomes practically necessary, so that renaming the SKU automatically propagates to every referencing foreign key column.

If ON UPDATE CASCADE is missing in such a case, every update to the primary key fails with a constraint error as long as referencing rows exist, which in practice forces either abandoning the change entirely or manually updating every dependent row first before the change to the primary key becomes possible. This behavior is another strong argument for combining referential integrity with stable surrogate keys from the start, where ON UPDATE questions never become practically relevant in the first place.

-- ON UPDATE CASCADE only matters when the referenced key can change
-- Relevant with a natural key such as SKU as the primary key
CREATE TABLE products_sku_pk (
    sku   VARCHAR(50) PRIMARY KEY,
    name  VARCHAR(255) NOT NULL
);

CREATE TABLE order_items_sku (
    order_id  INT NOT NULL,
    sku       VARCHAR(50) NOT NULL,
    quantity  INT NOT NULL,
    PRIMARY KEY (order_id, sku),
    FOREIGN KEY (sku) REFERENCES products_sku_pk(sku)
        ON UPDATE CASCADE   -- SKU rename propagates automatically
        ON DELETE RESTRICT  -- but deleting a referenced SKU is blocked
);

-- Renaming the SKU now cascades to every order_items row automatically
UPDATE products_sku_pk SET sku = 'NEW-SKU-001' WHERE sku = 'OLD-SKU-001';

7. Why disabling FK checks corrupts data

Some developers disable FK checks temporarily, for example with SET FOREIGN_KEY_CHECKS=0 in MySQL or DISABLE TRIGGER ALL in PostgreSQL, to speed up bulk imports or work around delete orders that would otherwise fail because of existing foreign key constraints. The problem: if a row that points to a non-existent parent row gets inserted during this disabled phase, or a parent row gets deleted while children still exist, the database accepts it without complaint. Referential integrity is broken from that point on, without any error ever surfacing.

This corruption often only shows up weeks or months later, when an application tries to load the associated parent row through a JOIN and gets an empty result where a record was actually expected. By that point, the inconsistent row may already have propagated further, for example through backups, replication or data exports that carry over the already-corrupt data unchanged. Repairing a data set that got corrupted this way often requires extensive manual analysis to figure out which orphaned rows even exist and how to handle them correctly from a business perspective. Anyone who takes referential integrity seriously never disables FK checks for regular operation, and at most for controlled, carefully monitored one-off migrations, where integrity is explicitly re-checked immediately afterward.

-- DANGEROUS pattern: disabling FK checks silently allows orphaned rows
SET FOREIGN_KEY_CHECKS = 0;
DELETE FROM customers WHERE customer_id = 42;  -- orders now reference nothing
SET FOREIGN_KEY_CHECKS = 1;
-- No error was raised, but referential integrity is now broken silently

-- SAFE alternative: handle the dependency explicitly, in the right order
DELETE FROM order_lines WHERE order_id IN (
    SELECT order_id FROM orders WHERE customer_id = 42
);
DELETE FROM orders WHERE customer_id = 42;
DELETE FROM customers WHERE customer_id = 42;
-- Every step stays covered by active foreign key constraints

-- Detecting existing orphans after a corruption, as a recovery check
SELECT o.order_id FROM orders AS o
LEFT JOIN customers AS c ON c.customer_id = o.customer_id
WHERE c.customer_id IS NULL;

8. Indexing foreign key columns

An often overlooked aspect of foreign keys is that many database engines, including MySQL with InnoDB, automatically create an index on the foreign key column, while PostgreSQL does not. Without an explicit index on the foreign key column, every delete or update of the referenced parent row has to scan the entire child table sequentially to check whether dependent rows exist, which causes significant performance problems on large tables.

The rule of thumb for referential integrity in practice: every foreign key column should be indexed explicitly, regardless of whether the database engine in use does this automatically or not. This index speeds up not just the referential check during CASCADE and RESTRICT operations, but also the far more frequent JOIN queries that use exactly this foreign key column for the link. A missing index on a heavily trafficked foreign key column is one of the most common and easiest to fix performance mistakes in production databases.

-- PostgreSQL does NOT auto-index foreign key columns, unlike MySQL/InnoDB
-- Always add the index explicitly for both integrity checks and JOINs
CREATE TABLE orders (
    order_id    INT PRIMARY KEY,
    customer_id INT NOT NULL,
    FOREIGN KEY (customer_id) REFERENCES customers(customer_id)
);

-- Without this index, DELETE FROM customers and every JOIN scan the table
CREATE INDEX idx_orders_customer_id ON orders (customer_id);

-- Composite foreign keys need a composite index in the same column order
CREATE TABLE shipments (
    shipment_id  INT PRIMARY KEY,
    order_id     INT NOT NULL,
    product_id   INT NOT NULL,
    FOREIGN KEY (order_id, product_id) REFERENCES order_lines(order_id, product_id)
);
CREATE INDEX idx_shipments_order_product ON shipments (order_id, product_id);

9. Vendor differences: MySQL, PostgreSQL, SQL Server

All three major relational databases support the basic FOREIGN KEY syntax and all five ON DELETE / ON UPDATE options largely in a standard-compliant way, but there are practically relevant differences. In MySQL, only the InnoDB storage engine enforces foreign key constraints, the older MyISAM engine accepts the syntax but ignores the constraint entirely, a frequent trap in older or misconfigured MySQL installations.

PostgreSQL additionally offers DEFERRABLE constraints, which allow postponing the check of referential integrity until the end of a transaction, instead of running it immediately with every single statement. This is useful with circular dependencies within a single transaction, where a strict, immediate check would fail incorrectly even though the final state after the whole transaction is consistent. SQL Server, in turn, forbids multiple cascading paths leading to the same target by default, so-called "multiple cascade paths", and requires setting one of the paths to NO ACTION manually and resolving it with a trigger instead in such cases. These differences are rarely the core of an architecture decision, but should be known before a schema gets ported between database systems.

10. Summary

Foreign keys are the mechanism a database uses to enforce referential integrity itself, regardless of which path data changes take. ON DELETE CASCADE belongs to true ownership relationships, RESTRICT protects critical core entities from accidental deletion, SET NULL and SET DEFAULT fit optional relationships. ON UPDATE CASCADE becomes relevant mainly with natural-key primary keys that can change, while surrogate keys mostly avoid this problem from the start.

Disabling FK checks for supposed convenience is almost always a mistake, because it forces the database to accept inconsistent states without complaint, and repairing them later is considerably more expensive than the original inconvenience of following the correct delete order. Explicit indexes on every foreign key column are mandatory, regardless of whether the database engine creates them automatically, because they speed up both integrity checks and the far more common JOIN queries.

11. FAQ: Foreign keys and referential integrity