SQL — Foreign Key

What is a Foreign Key?

A foreign key is a column that references the primary key of another table. It ensures that a value in one table must exist in the referenced table.

Basic Syntax

CREATE TABLE orders (
  id INT PRIMARY KEY AUTO_INCREMENT,
  customer_id INT,
  FOREIGN KEY (customer_id) REFERENCES customers(id)
);

Full Example

CREATE TABLE customers (
  id INT PRIMARY KEY AUTO_INCREMENT,
  name VARCHAR(100) NOT NULL,
  email VARCHAR(100) NOT NULL UNIQUE
);

CREATE TABLE orders (
  id INT PRIMARY KEY AUTO_INCREMENT,
  customer_id INT NOT NULL,
  amount DECIMAL(10, 2),
  order_date DATE DEFAULT (CURRENT_DATE),
  FOREIGN KEY (customer_id) REFERENCES customers(id)
);

Now this fails:

INSERT INTO orders (customer_id, amount) VALUES (999, 50.00);
-- ERROR: customer 999 doesn't exist

Named Foreign Key

CREATE TABLE orders (
  id INT PRIMARY KEY AUTO_INCREMENT,
  customer_id INT NOT NULL,
  CONSTRAINT fk_orders_customer
  FOREIGN KEY (customer_id) REFERENCES customers(id)
);

ON DELETE Actions

What happens when the referenced row is deleted:

Action	Behavior
CASCADE	Delete the dependent rows too
SET NULL	Set the foreign key to NULL
SET DEFAULT	Set to the default value
RESTRICT	Block the delete
NO ACTION	Block the delete (same as RESTRICT)

CREATE TABLE orders (
  id INT PRIMARY KEY AUTO_INCREMENT,
  customer_id INT NOT NULL,
  FOREIGN KEY (customer_id) REFERENCES customers(id)
    ON DELETE CASCADE
    ON UPDATE CASCADE
);

• ON DELETE CASCADE — deleting a customer also deletes all their orders
• ON DELETE RESTRICT — prevents deleting a customer who has orders

ON UPDATE Actions

FOREIGN KEY (customer_id) REFERENCES customers(id)
  ON UPDATE CASCADE

If a customer's id changes, all referencing orders update automatically.

Adding Foreign Key to Existing Table

ALTER TABLE orders
ADD CONSTRAINT fk_customer
FOREIGN KEY (customer_id) REFERENCES customers(id);

Dropping a Foreign Key

-- MySQL
ALTER TABLE orders
DROP FOREIGN KEY fk_customer;

-- PostgreSQL
ALTER TABLE orders
DROP CONSTRAINT fk_customer;

Self-Referencing Foreign Key

A table can reference itself:

CREATE TABLE employees (
  id INT PRIMARY KEY AUTO_INCREMENT,
  name VARCHAR(100),
  manager_id INT,
  FOREIGN KEY (manager_id) REFERENCES employees(id)
);

Composite Foreign Key

Reference a composite primary key:

CREATE TABLE order_items (
  order_id INT,
  item_number INT,
  product_id INT,
  FOREIGN KEY (order_id, item_number)
    REFERENCES orders(id, item_number)
);

Foreign Key vs Application Logic

Database foreign keys enforce integrity regardless of which application writes to the data. Application-level checks can be bypassed — database constraints cannot.

Best Practices

• Always use foreign keys to maintain referential integrity
• Use ON DELETE RESTRICT for critical data (force explicit cleanup)
• Use ON DELETE CASCADE only when deletion should truly propagate
• Name your foreign keys for easier management
• Index foreign key columns for better join performance

Practice

1. Create authors and books tables with a foreign key relationship
2. Try inserting a book with a non-existent author
3. Set up ON DELETE CASCADE and verify that deleting an author removes their books
4. Create a self-referencing foreign key for an organizational hierarchy