MySQL FOREIGN KEY Constraint

A foreign key is a column in one table that references the primary key of another table. It creates a real, enforced link between the two tables and ensures that the relationship between them stays consistent — a rule known as referential integrity.

For example, if you have an orders table and a customers table, a foreign key on orders.customer_id pointing to customers.id ensures that every order always belongs to a real customer. You cannot create an order for a customer that does not exist.

---

Parent Table and Child Table

When working with foreign keys, two terms are important:

• Parent table — the table that holds the referenced column (usually the primary key side).
• Child table — the table that holds the foreign key column (the side that references the parent).

---

Syntax

CONSTRAINT constraint_name
FOREIGN KEY (child_column)
REFERENCES parent_table (parent_column)

---

Creating Tables with a FOREIGN KEY

First, create the parent table:

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

Then create the child table with a foreign key pointing back to customers:

CREATE TABLE orders (
    id INT NOT NULL AUTO_INCREMENT,
    customer_id INT NOT NULL,
    total DECIMAL(10, 2) NOT NULL,
    order_date DATE NOT NULL,
    PRIMARY KEY (id),
    CONSTRAINT fk_orders_customer
        FOREIGN KEY (customer_id)
        REFERENCES customers (id)
);

---

Referential Integrity in Action

With this foreign key in place, MySQL enforces the relationship automatically:

-- This works: customer with id=1 exists
INSERT INTO orders (customer_id, total, order_date)
VALUES (1, 2500.00, '2025-06-01');

-- This fails: no customer with id=99
INSERT INTO orders (customer_id, total, order_date)
VALUES (99, 1200.00, '2025-06-01');
-- ERROR 1452: Cannot add or update a child row: a foreign key constraint fails

---

ON DELETE and ON UPDATE Options

You can control what happens to child rows when a parent row is deleted or updated. These are set using ON DELETE and ON UPDATE clauses:

Option	What happens to child rows
RESTRICT	Prevents the parent from being deleted or updated if child rows exist. This is the default behavior.
CASCADE	Automatically deletes or updates child rows when the parent row is deleted or updated.
SET NULL	Sets the foreign key column in child rows to NULL when the parent is deleted or updated.
NO ACTION	Same as RESTRICT in MySQL — the operation is rejected if child rows exist.

Example using ON DELETE CASCADE — if a customer is deleted, all their orders are automatically deleted too:

CONSTRAINT fk_orders_customer
    FOREIGN KEY (customer_id)
    REFERENCES customers (id)
    ON DELETE CASCADE
    ON UPDATE CASCADE

---

Adding a FOREIGN KEY to an Existing Table

Use ALTER TABLE to add a foreign key after the table is created:

ALTER TABLE orders
ADD CONSTRAINT fk_orders_customer
    FOREIGN KEY (customer_id)
    REFERENCES customers (id)
    ON DELETE RESTRICT;

---

Dropping a FOREIGN KEY

To remove a foreign key, you must know its constraint name:

ALTER TABLE orders
DROP FOREIGN KEY fk_orders_customer;

You can find all foreign key constraint names in a table using:

SHOW CREATE TABLE orders;

---

Key Points to Remember

• A FOREIGN KEY links a column in a child table to the primary key of a parent table.
• It enforces referential integrity — you cannot insert a child row that references a non-existent parent.
• The parent table must exist and have a primary key before the child table's foreign key can reference it.
• Use ON DELETE CASCADE to automatically remove child rows when a parent is deleted.
• The default behavior (RESTRICT) blocks deletion of a parent row if any child rows reference it.
• Always name your foreign key constraints — it makes them much easier to manage and drop later.