Foreign keys enforce relationships between tables, ensuring data consistency. They prevent orphaned records and maintain referential integrity across your database.

Creating a Foreign Key

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

Adding Foreign Key to Existing Table

ALTER TABLE order_items
ADD CONSTRAINT fk_order
FOREIGN KEY (order_id) REFERENCES orders(order_id);

Cascade Operations

CASCADE DELETE

-- When customer is deleted, all their orders are deleted too
CREATE TABLE orders (
    order_id INT PRIMARY KEY,
    customer_id INT,
    FOREIGN KEY (customer_id) REFERENCES customers(customer_id)
        ON DELETE CASCADE
);

CASCADE UPDATE

-- When customer_id changes, update all related orders
CREATE TABLE orders (
    order_id INT PRIMARY KEY,
    customer_id INT,
    FOREIGN KEY (customer_id) REFERENCES customers(customer_id)
        ON UPDATE CASCADE
);

SET NULL Option

-- When department is deleted, set employee's dept_id to NULL
CREATE TABLE employees (
    employee_id INT PRIMARY KEY,
    dept_id INT,
    FOREIGN KEY (dept_id) REFERENCES departments(dept_id)
        ON DELETE SET NULL
);

Common Cascade Options

• CASCADE: Automatically delete/update related rows
• SET NULL: Set foreign key to NULL when parent is deleted
• RESTRICT: Prevent deletion if related records exist (default)
• NO ACTION: Similar to RESTRICT, check at transaction end