SQL Foriegn Key

1. Introduction to Foreign Keys

In relational databases, a FOREIGN KEY is a mechanism that enforces a link between data in two tables. Conceptually, it is like saying “the value in this column must match one of the values in the primary key column of another table.” This link ensures consistency: you cannot reference a row that does not exist.

For beginners, think of a foreign key as a way to connect child records to parent records. For example, an orders table might have a customer_id column that refers back to the customers table. Every order must belong to an existing customer.

2. Purpose and Referential Integrity

The main purpose of foreign keys is to maintain referential integrity. Referential integrity means that relationships between tables remain valid: child rows always reference existing parent rows, and parent rows cannot be deleted if children still exist (unless explicitly allowed).

Without foreign keys, you could accidentally insert orders for a non‑existent customer, or delete a customer record while orphaning their orders. Foreign keys guard against these anomalies, making your database reliable.

3. Defining Foreign Keys

3.1 Inline Definition

CREATE TABLE customers (
  customer_id INT PRIMARY KEY,
  name        VARCHAR(100) NOT NULL
);

CREATE TABLE orders (
  order_id    INT PRIMARY KEY,
  order_date  DATE,
  customer_id INT REFERENCES customers(customer_id)
);

Here, customer_id in orders is defined inline with REFERENCES customers(customer_id). This creates a foreign key constraint named by the database.

3.2 Out‑of‑line Definition

CREATE TABLE orders (
  order_id    INT PRIMARY KEY,
  order_date  DATE,
  customer_id INT,
  FOREIGN KEY (customer_id) REFERENCES customers(customer_id)
);

This syntax separates the column list from the constraint, which is useful for adding multiple keys or composite references.

4. Composite Foreign Keys

Sometimes the relationship involves multiple columns. For example, linking order details to orders:

CREATE TABLE order_details (
  order_id    INT,
  product_id  INT,
  quantity    INT,
  PRIMARY KEY (order_id, product_id),
  FOREIGN KEY (order_id) REFERENCES orders(order_id)
);

Here the composite primary key (order_id, product_id) uniquely identifies each detail line. We still reference only order_id, but you could also reference composite keys if needed.

5. Altering Tables to Add or Drop Foreign Keys

5.1 Add a Foreign Key

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

5.2 Drop a Foreign Key

-- MySQL syntax
ALTER TABLE orders
DROP FOREIGN KEY fk_customer;

-- PostgreSQL syntax
ALTER TABLE orders
DROP CONSTRAINT fk_customer;

6. Cascading Actions

Foreign keys can automatically propagate changes using ON DELETE and ON UPDATE actions:

CASCADE: delete or update child rows when the parent changes.

SET NULL: set the foreign key column to NULL in child rows.

RESTRICT or NO ACTION: prevent the change if child rows exist.

CREATE TABLE orders (
  order_id    INT PRIMARY KEY,
  customer_id INT,
  FOREIGN KEY (customer_id)
    REFERENCES customers(customer_id)
    ON DELETE CASCADE
    ON UPDATE CASCADE
);

With ON DELETE CASCADE, deleting a customer removes all their orders automatically. Use this with care to avoid unintentional data loss.

7. Self‑Referential Foreign Keys

A table can reference itself to model hierarchical data, such as an employee reporting structure:

CREATE TABLE employees (
  employee_id INT PRIMARY KEY,
  name        VARCHAR(50),
  manager_id  INT,
  FOREIGN KEY (manager_id) REFERENCES employees(employee_id)
);

Here, manager_id points to another row in the same employees table, forming a tree of reporting relationships.

8. Performance Considerations

Foreign keys add overhead on inserts, updates, and deletes because the database must check constraints. To optimize:

Index both primary key and foreign key columns for efficient lookups.

Batch large deletes or updates in chunks.

Disable constraints temporarily during bulk loads, if safe, then re-enable.

9. Best Practices

Always define foreign keys for meaningful relationships.

Give constraints descriptive names (e.g., fk_orders_customers).

Use cascading actions judiciously—prefer RESTRICT for critical data.

Avoid circular cascades, which can lead to complexity.

10. Combined Example

CREATE TABLE customers (
  customer_id INT PRIMARY KEY,
  name        VARCHAR(100) NOT NULL
);

CREATE TABLE orders (
  order_id    INT PRIMARY KEY,
  order_date  DATE DEFAULT CURRENT_DATE,
  customer_id INT,
  CONSTRAINT fk_orders_customers
    FOREIGN KEY (customer_id)
    REFERENCES customers(customer_id)
    ON DELETE SET NULL
);

CREATE TABLE order_details (
  order_id    INT,
  product_id  INT,
  quantity    INT CHECK (quantity > 0),
  PRIMARY KEY (order_id, product_id),
  FOREIGN KEY (order_id) REFERENCES orders(order_id)
);

This schema links customers to orders, orders to details, and gracefully handles deleted customers by nulling customer_id on orders.

11. Summary

Foreign keys are the backbone of relational integrity, preventing orphaned records and ensuring consistency across tables. By understanding how to define, alter, and use cascading actions, beginners can model complex relationships with confidence.