Referential Integrity, Cascades and Constraint Management

Concept

Referential integrity is the guarantee that every foreign key value points at a row that exists. The database enforces it on insert, on update and on delete - and the ON DELETE and ON UPDATE clauses decide how.

The referential actions

Syntax

CONSTRAINT fk_order_items_order FOREIGN KEY (order_id)
    REFERENCES orders(id)
    ON DELETE CASCADE
    ON UPDATE RESTRICT

Example

-- Line items cannot exist without their order
CREATE TABLE order_items (
    order_id   INT,
    product_id INT,
    quantity   INT NOT NULL,
    PRIMARY KEY (order_id, product_id),
    CONSTRAINT fk_oi_order FOREIGN KEY (order_id)
        REFERENCES orders(id) ON DELETE CASCADE
);

-- An employee survives their department being closed
CREATE TABLE employees_v2 (
    id      INT PRIMARY KEY,
    name    VARCHAR(60) NOT NULL,
    dept_id INT NULL,
    CONSTRAINT fk_emp_dept_v2 FOREIGN KEY (dept_id)
        REFERENCES departments(id) ON DELETE SET NULL
);

Explanation

The choice is a business decision, not a technical one. Ask: if the parent disappears, does the child still mean anything?

• An order line without an order is meaningless - CASCADE.
• An employee without a department is a real employee - SET NULL.
• A customer with orders should not be deletable at all - RESTRICT.

SET NULL requires the child column to be nullable; combining it with NOT NULL is a contradiction the database rejects at creation time.

Cascades are recursive. Deleting a customer with ON DELETE CASCADE to orders, which cascades to order items, removes all three levels from one statement. That is powerful and easy to underestimate - which is why this notes application's own schema uses cascades for note tags and links, but a soft deleted_at flag for notes themselves.

Managing constraints

-- Add a constraint to an existing table (the data must already satisfy it)
ALTER TABLE orders
  ADD CONSTRAINT fk_orders_customer FOREIGN KEY (customer_id) REFERENCES customers(id);

-- Drop it - MySQL and MariaDB
ALTER TABLE orders DROP FOREIGN KEY fk_orders_customer;

-- Drop it - PostgreSQL, SQL Server, Oracle
ALTER TABLE orders DROP CONSTRAINT fk_orders_customer;

-- Temporarily suspend checking during a bulk load (MySQL)
SET FOREIGN_KEY_CHECKS = 0;
-- ... load data ...
SET FOREIGN_KEY_CHECKS = 1;

Disabling checks is a loading tool, not an operating mode. MySQL does not revalidate existing rows when you turn checks back on, so any orphan created while they were off stays there silently. Validate afterwards:

-- Find orphans that should not exist
SELECT o.id, o.customer_id
FROM   orders o
LEFT JOIN customers c ON c.id = o.customer_id
WHERE  o.customer_id IS NOT NULL AND c.id IS NULL;

Important rules

• RESTRICT and NO ACTION behave the same in MySQL; in PostgreSQL NO ACTION can be deferred to the end of the transaction and RESTRICT cannot.
• ON DELETE SET NULL requires a nullable child column.
• Cascading deletes fire recursively and do not fire DELETE triggers in every engine - check before relying on audit triggers.
• Adding a foreign key to a populated table fails if any orphan exists.
• Constraint names must be unique per schema in MySQL, and per table in some other products.

Common mistakes

• Using CASCADE everywhere and losing a large subtree from one careless delete.
• Turning off FOREIGN_KEY_CHECKS for a bulk load and never validating afterwards.
• Combining ON DELETE SET NULL with a NOT NULL column.
• Assuming a cascade fires application level logic. It does not - it happens inside the database.
• Dropping constraints to make a migration pass, then forgetting to restore them.

Best practices

• Default to RESTRICT. Choose CASCADE only for rows that are genuinely part of the parent.
• Prefer soft deletes for business records humans may need back.
• After any bulk load with checks disabled, run an orphan query per foreign key.
• Keep constraint definitions in migration files so the rules are reviewable in version control.

Practice

1. Choose an action for each: order_items.order_id, employees.manager_id, orders.customer_id. Justify each choice in one line.
2. Write the orphan detection query for assignments.project_id.
3. What happens to assignments when a project is deleted, given the sample schema?