MySQL - foreign key constraints

1. What is a Foreign Key Constraint?

A foreign key is a column (or a set of columns) in one table that refers to the primary key (or unique key) of another table.

It is used to enforce referential integrity between two tables. In simpler terms, it ensures that relationships between tables are consistent.

Example:

  • Table users has user_id as primary key.

  • Table orders has user_id as a foreign key pointing to users.user_id.
    This ensures that every order must belong to a valid user.

2. Why Use Foreign Keys?

  • Data Integrity: Prevent invalid data (e.g., an order referencing a non-existent user).

  • Cascading Actions: Automatically update or delete related records.

  • Structured Relationships: Makes the database relational and logical.

3. Syntax

Creating a Foreign Key During Table Creation

CREATE TABLE users (
    user_id INT PRIMARY KEY,
    name VARCHAR(50)
);

CREATE TABLE orders (
    order_id INT PRIMARY KEY,
    order_date DATE,
    user_id INT,
    CONSTRAINT fk_user
        FOREIGN KEY (user_id)
        REFERENCES users(user_id)
        ON DELETE CASCADE
        ON UPDATE CASCADE
);

  • fk_user: Name of the foreign key constraint.

  • ON DELETE CASCADE: If a user is deleted, all their orders are deleted automatically.

  • ON UPDATE CASCADE: If the user_id changes, it updates in the orders table automatically.

Adding a Foreign Key to an Existing Table

ALTER TABLE orders
ADD CONSTRAINT fk_user
FOREIGN KEY (user_id) REFERENCES users(user_id)
ON DELETE CASCADE
ON UPDATE CASCADE;

4. Foreign Key Options

Option Description
ON DELETE CASCADE Deletes related rows when parent row is deleted.
ON DELETE SET NULL Sets foreign key column to NULL when parent row is deleted.
ON DELETE RESTRICT Prevents deletion of parent row if child exists (default behavior).
ON UPDATE CASCADE Updates child rows if parent key is updated.
ON UPDATE SET NULL Sets child foreign key to NULL if parent key is updated.

5. Rules & Best Practices

  1. Data Types Must Match: Foreign key column must have the same type and size as the referenced primary key.

  2. Indexed Column: The referenced column must be primary key or unique.

  3. Naming: Use a clear naming convention like fk_table_column.

  4. Avoid Circular References: Tables referencing each other in a loop can cause errors.

6. Example

CREATE TABLE customers (
    customer_id INT PRIMARY KEY,
    name VARCHAR(50)
);

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

  • If a customer is deleted, all their orders are automatically deleted due to ON DELETE CASCADE.

Foreign keys are critical for relational databases because they prevent "orphaned" records and enforce consistent relationships.