MYSQL Tutorial



MySQL FOREIGN KEY


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.

🌟 Enjoyed Learning with Us?

Help others discover Technorank Learning by sharing your honest experience.
Your support inspires us to keep building!

Leave a Google Review