MYSQL Tutorial

MySQL FOREIGN KEY

A FOREIGN KEY enforces referential integrity by ensuring that a value in one table matches a primary (or unique) key in another table. It prevents “orphan” records and maintains relationships.

Defining a Foreign Key on CREATE

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

ON DELETE CASCADE removes orders if the customer is deleted; ON UPDATE CASCADE propagates changes to the key.

Adding a Foreign Key to an Existing Table

ALTER TABLE order_items
ADD CONSTRAINT fk_order
  FOREIGN KEY (order_id)
    REFERENCES orders(order_id)
    ON DELETE RESTRICT
    ON UPDATE CASCADE;
  

Dropping a Foreign Key

ALTER TABLE order_items
DROP FOREIGN KEY fk_order;
  

Key Tips & Best Practices

• Name constraints clearly (e.g., fk_table_refTable).
• Choose appropriate actions: CASCADE, RESTRICT, SET NULL, or NO ACTION.
• Ensure referenced column is indexed (PRIMARY or UNIQUE key).
• Use INNODB storage engine to support foreign keys.
• Test deletions and updates to verify referential actions behave as expected.