FOREIGN KEY in SQL: Referential Integrity, ON DELETE CASCADE, and ON UPDATE

Tables rarely exist in isolation — an order belongs to a customer, an enrollment belongs to a student and a course. FOREIGN KEY is the constraint that formally links one table to another and protects that relationship from becoming inconsistent. This lesson explains referential integrity, the FOREIGN KEY syntax, and the critical ON DELETE and ON UPDATE behaviors that decide what happens to related rows when their parent changes.

What Is a FOREIGN KEY?

A FOREIGN KEY is a column (or set of columns) in one table that references the PRIMARY KEY of another table, creating a formal relationship between them. It enforces referential integrity, meaning the database will not allow a row to reference a parent row that doesn't exist — you can't insert an order for a customer_id that isn't in the customers table.

What You'll Learn

• Define FOREIGN KEY constraints linking a child table to a parent table.
• Understand referential integrity and why it matters for data consistency.
• Choose the correct ON DELETE behavior: CASCADE, SET NULL, or RESTRICT.
• Apply ON UPDATE CASCADE to keep related rows in sync automatically.

Key Terms to Know

• Foreign key: A column referencing another table's primary key to establish a relationship.
• Referential integrity: The guarantee that a foreign key value always matches an existing primary key value in the parent table.
• Parent table: The table whose primary key is being referenced.
• Child table: The table containing the foreign key that references the parent.
• ON DELETE / ON UPDATE: Clauses defining what happens to child rows when the referenced parent row is deleted or updated.

How FOREIGN KEY Enforces Referential Integrity

Without a foreign key, nothing stops an orders table from storing a customer_id value of 9999 even if no customer with that ID exists — this is called an orphaned record, and it silently corrupts reports and breaks joins. A FOREIGN KEY constraint on orders.customer_id referencing customers.customer_id rejects any insert or update that would create such an orphan.

This relationship also protects the parent side by default: MySQL will refuse to delete a customer row if orders still reference it, unless you explicitly configure different behavior using ON DELETE.

ON DELETE Behaviors: CASCADE, SET NULL, and RESTRICT

ON DELETE CASCADE automatically deletes child rows when their parent row is deleted — deleting a customer would also delete all of their orders. This is convenient but dangerous if used carelessly, since it can silently delete large amounts of related data.

ON DELETE SET NULL sets the foreign key column to NULL instead of deleting the child row, which only works if that column allows NULL. ON DELETE RESTRICT (the default behavior in many systems) blocks the deletion of the parent entirely if any child rows still reference it, forcing you to handle related data explicitly first.

ON UPDATE CASCADE: Keeping References in Sync

ON UPDATE CASCADE automatically updates the foreign key value in child rows if the referenced primary key value in the parent ever changes. This matters less when primary keys are AUTO_INCREMENT integers that never change, but becomes important if a primary key is a natural key, like a product SKU, that could legitimately be renamed.

In practice, most production schemas use ON UPDATE CASCADE paired with ON DELETE RESTRICT or ON DELETE CASCADE depending on whether child data should survive or disappear along with its parent.

Visual Summary

Picture a parent table as a master list of approved ID badges, and a child table as a sign-in sheet that only accepts badge numbers from that master list. A FOREIGN KEY is the security guard checking every sign-in entry against the master list. ON DELETE CASCADE means revoking a badge automatically erases all of that person's sign-in entries; ON DELETE RESTRICT means the guard refuses to revoke a badge until all of that person's entries are cleared first.

ON DELETE Behavior Comparison

Option	Effect on Child Rows	Typical Use Case
CASCADE	Child rows are automatically deleted	Order items when an order is deleted
SET NULL	Foreign key column is set to NULL	Optional manager_id when a manager leaves
RESTRICT	Parent deletion is blocked if children exist	Customers with existing orders (default-safe choice)
NO ACTION	Similar to RESTRICT in MySQL	Same as RESTRICT in most MySQL versions

SQL Example

-- Parent table
CREATE TABLE customers (
  customer_id INT PRIMARY KEY AUTO_INCREMENT,
  full_name   VARCHAR(100) NOT NULL
);

-- Child table referencing customers
CREATE TABLE orders (
  order_id    INT PRIMARY KEY AUTO_INCREMENT,
  customer_id INT NOT NULL,
  order_total DECIMAL(10, 2) NOT NULL,
  FOREIGN KEY (customer_id)
    REFERENCES customers(customer_id)
    ON DELETE RESTRICT
    ON UPDATE CASCADE
);

-- A second child table where CASCADE makes sense
CREATE TABLE order_items (
  order_item_id INT PRIMARY KEY AUTO_INCREMENT,
  order_id      INT NOT NULL,
  product_name  VARCHAR(100) NOT NULL,
  quantity      INT NOT NULL,
  FOREIGN KEY (order_id)
    REFERENCES orders(order_id)
    ON DELETE CASCADE
);

orders.customer_id uses ON DELETE RESTRICT, so a customer with existing orders can't be deleted by accident — you'd have to handle their orders first. order_items.order_id uses ON DELETE CASCADE, since deleting an entire order should reasonably delete its line items too, as they have no meaning without the parent order.

Real-World Examples

• E-commerce platforms use ON DELETE RESTRICT on customer-order relationships to prevent accidentally erasing order history when a customer account is removed.
• Content platforms use ON DELETE CASCADE between a blog post and its comments, since comments have no meaning if the post is deleted.
• HR systems use ON DELETE SET NULL on an employee's manager_id when the manager leaves the company, leaving the employee record intact but unassigned.
• Banking systems almost always restrict deletion of account or customer records that have any transaction history, for both data integrity and regulatory reasons.

Best Practices and Pro Tips

• Default to ON DELETE RESTRICT for relationships involving financial or audit-critical data — explicit, deliberate deletion is safer than silent cascading deletes.
• Reserve ON DELETE CASCADE for true parent-dependent data, like order items or comments, where the child record is genuinely meaningless without its parent.
• Always index foreign key columns (MySQL does this automatically when the constraint is created), since joins on unindexed foreign keys can become a serious performance bottleneck as tables grow.

Common Mistakes to Avoid

• Using ON DELETE CASCADE everywhere without thinking it through, leading to accidental mass-deletion of unrelated data.
• Forgetting that the referenced parent column must be a PRIMARY KEY or UNIQUE column — foreign keys can't reference arbitrary non-unique columns.
• Mismatching data types between the foreign key and the referenced primary key, which MySQL will reject when creating the constraint.
• Not considering ON UPDATE behavior at all, leaving cascading updates unhandled if a natural key value ever changes.

Interview Questions

Q1. What is referential integrity, and how does FOREIGN KEY enforce it?

Referential integrity means a foreign key value must always correspond to an existing primary key value in the parent table. FOREIGN KEY enforces this by rejecting any insert or update that would create a reference to a non-existent parent row.

Q2. What is the difference between ON DELETE CASCADE and ON DELETE RESTRICT?

ON DELETE CASCADE automatically deletes child rows when their referenced parent row is deleted. ON DELETE RESTRICT blocks the deletion of the parent row entirely if any child rows still reference it.

Q3. When would you use ON DELETE SET NULL instead of CASCADE?

SET NULL is appropriate when the child row should still exist after its parent is deleted, but the relationship itself should be cleared, such as an employee's manager_id when that manager leaves the company. The foreign key column must allow NULL for this to work.

Q4. Can a foreign key reference a column that isn't a primary key?

Yes, but the referenced column must have a UNIQUE constraint (or be a primary key) in the parent table, since a foreign key must always reference a column guaranteed to have unique values.

Practice MCQs

1. What does a FOREIGN KEY constraint primarily enforce?

1. Column uniqueness
2. Referential integrity between tables
3. Automatic value generation
4. Character set compatibility

Answer: B. Referential integrity between tables

Explanation: A foreign key ensures values in the child table always correspond to existing values in the parent table's referenced column.

2. Which ON DELETE option automatically deletes related child rows?

1. RESTRICT
2. SET NULL
3. CASCADE
4. NO ACTION

Answer: C. CASCADE

Explanation: ON DELETE CASCADE removes child rows automatically whenever their referenced parent row is deleted.

3. Which ON DELETE option prevents deleting a parent row that still has child rows referencing it?

1. CASCADE
2. SET NULL
3. RESTRICT
4. ON UPDATE

Answer: C. RESTRICT

Explanation: RESTRICT blocks parent deletion entirely while related child rows still exist, requiring those child rows to be handled first.

Quick Revision Points

• Foreign keys must reference a PRIMARY KEY or UNIQUE column in the parent table.
• ON DELETE CASCADE deletes children with the parent; RESTRICT blocks deletion; SET NULL clears the reference.
• ON UPDATE CASCADE keeps child foreign key values in sync if the parent's referenced key changes.
• Orphaned records (child rows referencing non-existent parents) are exactly what foreign keys prevent.

Conclusion

• FOREIGN KEY is what turns separate tables into a connected, trustworthy relational structure.
• Choosing the right ON DELETE behavior is a deliberate design decision, not a default to leave unexamined.
• Referential integrity prevents an entire category of silent, hard-to-debug data corruption.

FOREIGN KEY constraints connect child tables to parent tables and enforce referential integrity, ensuring every reference points to a row that actually exists. ON DELETE CASCADE, SET NULL, and RESTRICT each define different, deliberate behavior for how child data should react to parent deletion, while ON UPDATE CASCADE keeps references synchronized if a parent key changes. With relationships between tables now understood, the next lesson covers ALTER TABLE — how to evolve a schema after it's already in use.