Part 35: Foreign Keys: Referential Integrity & Cascade Actions

A Foreign Key (FK) enforces a link between data in two tables, guaranteeing referential integrity.

1. Foreign Key Syntax

CREATE TABLE orders (
  id BIGSERIAL PRIMARY KEY,
  user_id BIGINT NOT NULL,
  total_amount DECIMAL(10, 2) NOT NULL,

  CONSTRAINT fk_orders_user 
    FOREIGN KEY (user_id) 
    REFERENCES users (id) 
    ON DELETE RESTRICT 
    ON UPDATE CASCADE
);

2. Cascade Actions

• ON DELETE RESTRICT / NO ACTION (Default): Prevents deleting the parent if child rows exist.
• ON DELETE CASCADE: Deletes all child rows automatically when the parent is deleted.
• ON DELETE SET NULL: Sets child foreign key to NULL.

Key Takeaways

• Foreign keys ensure orphan records cannot exist in your database.
• Always index foreign key columns to avoid full table locks and scans during joins!

In Part 36, we will explore Column Constraints: NOT NULL, UNIQUE, and CHECK!