SQL - Foreign Key in SQL

What Is a Foreign Key in SQL?

A foreign key is a column (or group of columns) in one table that links to the primary key in another table. It’s used to create a relationship between two tables, ensuring referential integrity—meaning the data in one table must correspond to valid data in another.

 Characteristics of a Foreign Key

1. Establishes a Relationship
   Connects two tables: the child table (which has the foreign key) references the parent table (which has the primary key).

2. Ensures Validity
   Values in the foreign key column must match values in the parent table’s primary key, or be NULL (if allowed).

3. Restricts or Cascades Actions
   You can define what happens to child records when the parent record is updated or deleted.

Example

Parent Table: customers

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

Child Table: orders

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

• customer_id in orders is a foreign key referencing customer_id in customers.

• This ensures that an order can only be placed by an existing customer.

Referential Actions

You can specify what should happen in the child table if the parent data is changed or deleted:

• ON DELETE CASCADE – Delete child rows when the parent is deleted.

• ON DELETE SET NULL – Set the foreign key to NULL when the parent is deleted.

• ON UPDATE CASCADE – Update child rows if the primary key is updated.

Example:

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

Why Use Foreign Keys?

• Maintain data consistency between related tables.

• Prevent orphan records (e.g., orders linked to non-existent customers).

• Enforce business rules through the structure of the database.

Visual Example

If you try to insert an order with customer_id = 3 and no such customer exists, the database will block the operation.