MySQL — Foreign Key

Basic Foreign Key

CREATE TABLE orders (
    id INT PRIMARY KEY,
    customer_id INT,
    total DECIMAL(10,2),
    FOREIGN KEY (customer_id) REFERENCES customers(id)
);

Named Foreign Key

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

ON DELETE Actions

Action	Behavior
CASCADE	Delete child rows when parent is deleted
SET NULL	Set foreign key to NULL
SET DEFAULT	Set to default value
NO ACTION	Prevent deletion (default)
RESTRICT	Prevent deletion (same as NO ACTION)

ON UPDATE Actions

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

Adding Foreign Key After Creation

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

Dropping Foreign Key

ALTER TABLE orders DROP FOREIGN KEY fk_customer;

Viewing Foreign Keys

SELECT * FROM information_schema.KEY_COLUMN_USAGE
WHERE REFERENCED_TABLE_NAME = 'customers';

Mini Practice

Write SQL code that:

1. Creates two related tables with a foreign key
2. Uses ON DELETE CASCADE
3. Uses ON DELETE SET NULL
4. Adds a foreign key to an existing table

Up Next

Continue with Unique — ensuring no duplicate values.