FOREIGN KEY

Status

Not started

Introduction to MySQL foreign key

A foreign key is a column or group of columns in a table that links to a column or group of columns in another table. The foreign key places constraints on data in the related tables, which allows MySQL to maintain referential integrity.

Let’s take a look at the following customers and orders tables from the sample database.

The relationship between customers table and orders table is one-to-many. This relationship is established via the foreign key in the orders table specified by the customerNumber column.
The customerNumber column in the orders table links to the customerNumber primary key column in the customers table.
The customers table is called the parent table or referenced table, and the orders table is known as the child table or referencing table.
Typically, the foreign key columns of the child table often refer to the primary key columns of the parent table.
A table can have more than one foreign key where each foreign key references a primary key of the different parent tables.
A table can only have a one primary key.
When a foreign key constraint is in place, it ensures that the values in the foreign key columns of the child table correspond to valid entries in the primary key columns of the parent table, or are set to NULL if allowed. This helps maintain referential integrity between the two tables.
Referential integrity ensures that relationships between tables remain consistent. For example, if a child table has a foreign key reference to a parent table, it must ensure that any value in the foreign key column either matches a value in the parent table's primary key column or is NULL (if allowed).
Foreign Key Constraint Actions: When defining a foreign key constraint, you can specify actions that should occur when a row in the parent table is updated or deleted. Common actions include:

SET NULL: If a row in the parent table is deleted or its primary key is updated, the corresponding foreign key values in the child table are set to NULL.
CASCADE: If a row in the parent table is deleted or its primary key is updated, the corresponding rows in the child table are also deleted or updated to match the new primary key. (dynamic)
RESTRICT: Prevents the deletion or update of a row in the parent table if there are corresponding rows in the child table.
NO ACTION: Similar to RESTRICT, it prevents the deletion or update of the parent row if there are matching foreign key values in the child table.
SET DEFAULT: Sets the foreign key value in the child table to a default value when the corresponding row in the parent table is deleted or updated.

Self-Referencing Foreign key

A self-referencing foreign key, also known as a recursive foreign key, is a foreign key constraint that references the primary key of the same table. This is useful for representing hierarchical data structures, such as organizational charts, categories, or tree structures, within a single table.

See the following employees table from the sample database.

The reportTo column is a foreign key that refers to the employeeNumber column which is the primary key of the employees table.

This relationship allows the employees table to store the reporting structure between employees and managers. Each employee reports to zero or one employee and an employee can have zero or many subordinates.

The foreign key on the column reportTo is known as a recursive or self-referencing foreign key.

MySQL FOREIGN KEY syntax

Here is the basic syntax of defining a foreign key constraint in the CREATE TABLE or ALTER TABLE statement:

CREATE TABLE child_table (
    column1 datatype PRIMARY KEY,
    column2 datatype,
    -- Other columns...

    [CONSTRAINT constraint_name]
    FOREIGN KEY [foreign_key_name] (column_name, ...)
    REFERENCES parent_table(column_name, ...)
    [ON DELETE reference_option]
    [ON UPDATE reference_option]
);

Explaining the Syntax:

1. CREATE TABLE child_table (...)

This starts the creation of a new table called child_table.
Inside the parentheses ( ... ), you define columns and constraints for that table.

Example:

CREATE TABLE students (...)

2. column1 datatype PRIMARY KEY

column1: The name of the column.
datatype: The type of values the column will store, such as INT, VARCHAR(100), DATE, etc.
PRIMARY KEY: Declares this column as the table’s primary key.

Must be unique (no duplicates).
Cannot be NULL (always requires a value).
Only one primary key per table (though it can be composite with multiple columns).

Example:

student_id INT PRIMARY KEY

3. column2 datatype

Just another regular column in the table.
You define it with a name and a datatype.
It may or may not have constraints (like NOT NULL, UNIQUE, etc.).

Example:

student_name VARCHAR(100)

4. - Other columns...

This is just a placeholder comment (not real SQL).
It means you can add as many columns as your table needs, each defined with a name, datatype, and optional constraint.

Example:

date_of_birth DATE,
email VARCHAR(150) UNIQUE

5. [CONSTRAINT constraint_name]

This part is optional.
You can give a custom name to your constraint (rule).
Naming constraints makes them easier to identify, drop, or modify later.
If you don’t give a name, the database will generate one automatically (often long and unreadable).

Example:

CONSTRAINT fk_student_class

6. FOREIGN KEY [foreign_key_name] (column_name, ...)

Declares a foreign key in the child table.
The foreign key column(s) must match values in another table (the parent table).
Ensures referential integrity (you cannot insert a child row if the parent does not exist).
foreign_key_name is optional, just like constraint_name, for labeling.

Example:

FOREIGN KEY (class_id)

7. REFERENCES parent_table(column_name, ...)

Defines which parent table and column(s) the foreign key points to.
This enforces that values in the child’s foreign key must exist in the parent’s primary key (or unique key).

Example:

REFERENCES classes(class_id)

This means: every class_id in the child table (students) must exist in the parent table (classes).

8. [ON DELETE reference_option]

Defines what happens to the child row if the parent row is deleted.
Options:

CASCADE: Delete the child rows automatically.
SET NULL: Set the child’s foreign key to NULL.
RESTRICT or NO ACTION: Prevent deletion if child rows exist.
SET DEFAULT: Set child’s foreign key to a default value (not supported in all databases).

Example:

ON DELETE CASCADE

If a class is deleted, all its students are also deleted.

9. [ON UPDATE reference_option]

Defines what happens to the child row if the parent key is updated.
Options:

CASCADE: Update the child’s foreign key automatically.
SET NULL: Set child’s foreign key to NULL.
RESTRICT or NO ACTION: Prevent update if child rows exist.
SET DEFAULT: Set child’s foreign key to a default value (not supported in all databases).

Example:

ON UPDATE CASCADE

If a parent’s class_id changes, the child’s class_id updates automatically.

Putting It All Together (Example)

-- Parent table
CREATE TABLE classes (
    class_id INT PRIMARY KEY,
    class_name VARCHAR(100) NOT NULL
);

-- Child table
CREATE TABLE students (
    student_id INT PRIMARY KEY,
    student_name VARCHAR(100),
    class_id INT,
    CONSTRAINT fk_student_class
        FOREIGN KEY (class_id)
        REFERENCES classes(class_id)
        ON DELETE CASCADE
        ON UPDATE CASCADE
);

students.class_id must exist in classes.class_id.
If a class is deleted → all its students are deleted too.
If a class_id changes → all related students get updated automatically.

Example 1

-- Create Categories table (needed by products)
CREATE TABLE categories (
    categoryId INT AUTO_INCREMENT PRIMARY KEY,
    categoryName VARCHAR(100) NOT NULL
) ENGINE=InnoDB;


-- Create Customers table
CREATE TABLE customers (
    customerId INT AUTO_INCREMENT PRIMARY KEY,
    customerName VARCHAR(100) NOT NULL
) ENGINE=InnoDB;


-- Create Orders table
CREATE TABLE orders (
    orderId INT AUTO_INCREMENT PRIMARY KEY,
    customerId INT NOT NULL,
    orderDate DATE NOT NULL,
    CONSTRAINT fk_orders_customer
        FOREIGN KEY (customerId)
        REFERENCES customers(customerId)
        ON DELETE RESTRICT
        ON UPDATE CASCADE
) ENGINE=InnoDB;

-- Create Products table
CREATE TABLE products (
    productId INT AUTO_INCREMENT PRIMARY KEY,
    productName VARCHAR(100) NOT NULL,
    categoryId INT NOT NULL,
    CONSTRAINT fk_products_category
        FOREIGN KEY (categoryId)
        REFERENCES categories(categoryId)
        ON DELETE RESTRICT
        ON UPDATE CASCADE
) ENGINE=InnoDB;

-- Create Order Items table
CREATE TABLE order_items (
    orderItemId INT AUTO_INCREMENT PRIMARY KEY,
    orderId INT NOT NULL,
    productId INT NOT NULL,
    quantity INT NOT NULL CHECK (quantity > 0),

    -- Prevent the same product being added twice to the same order
    CONSTRAINT uq_order_product UNIQUE (orderId, productId),

    CONSTRAINT fk_items_order
        FOREIGN KEY (orderId)
        REFERENCES orders(orderId)
        ON DELETE CASCADE
        ON UPDATE CASCADE,

    CONSTRAINT fk_items_product
        FOREIGN KEY (productId)
        REFERENCES products(productId)
        ON DELETE RESTRICT
        ON UPDATE CASCADE
) ENGINE=InnoDB;

🔍 Line-by-line, in your preferred style

‣

`CREATE TABLE customers (...)`

‣

`CREATE TABLE categories (...)`

‣

`CREATE TABLE products (...)`

‣

`CREATE TABLE orders (...)`

‣

`CREATE TABLE order_items (...)`

Why these `ON DELETE` / `ON UPDATE` choices?

RESTRICT on customers → orders, and categories → products

Protects history: you can’t delete a customer/category that has dependent rows.

CASCADE on orders → order_items

If an order is removed (e.g., test/bad data), its lines should go too.

CASCADE on all ON UPDATE FKs

Keeps references consistent if a parent key is ever updated (rare but safe).

You can change these to SET NULL if your FK columns allow NULL and you want to keep children but “detach” them when a parent is removed.

Quick ERD (ASCII)

customers (1) ──< orders (1) ──< order_items >── (1) products >── (1) categories
   PK customerId        PK orderId                 PK orderItemId        PK productId       PK categoryId
                        FK customerId              FK orderId             FK categoryId
                                                   FK productId

Legend: (1) one, < many, >── references

Mini demo (shows constraints working)

INSERT INTO categories (categoryName) VALUES ('Laptops'), ('Phones');
INSERT INTO products (productName, categoryId) VALUES ('ThinkPad X1', 1), ('iPhone 15', 2);

INSERT INTO customers (customerName) VALUES ('Teslim Ltd');
INSERT INTO orders (customerId, orderDate) VALUES (1, '2025-08-16');

-- Add items (works)
INSERT INTO order_items (orderId, productId, quantity) VALUES (1, 1, 2);

-- Try duplicate product for same order (fails due to uq_order_product)
-- INSERT INTO order_items (orderId, productId, quantity) VALUES (1, 1, 1);

-- Try delete product in use (fails due to RESTRICT)
-- DELETE FROM products WHERE productId = 1;

-- Delete order (cascades to its items)
DELETE FROM orders WHERE orderId = 1;

Notes & Gotchas (MySQL/InnoDB)

InnoDB automatically creates supporting indexes for FKs if missing.
CHECK constraints are enforced in MySQL 8.0.16+ only.
Use consistent naming for constraints (fk_<child>_<parent>); it simplifies maintenance.
Consider adding business rules like:

UNIQUE (customerName) if customer names must be unique (often they aren’t).
Additional columns (price at time of order, discounts) on order_items.

MySQL has five reference options: CASCADE, SET NULL, NO ACTION, RESTRICT, and SET DEFAULT.

CASCADE: if a row from the parent table is deleted or updated, the values of the matching rows in the child table are automatically deleted or updated.
SET NULL: if a row from the parent table is deleted or updated, the values of the foreign key column (or columns) in the child table are set to NULL.
RESTRICT: if a row from the parent table has a matching row in the child table, MySQL rejects deleting or updating rows in the parent table.
NO ACTION: is the same as RESTRICT.
SET DEFAULT: is recognized by the MySQL parser. However, this action is rejected by both InnoDB and NDB tables.

💡

MySQL fully supports three actions: RESTRICT, CASCADE and SET NULL.

💡

If you don’t specify the ON DELETE and ON UPDATE clause, the default action is RESTRICT

MySQL FOREIGN KEY examples

Let’s create a new database called fkdemo for the demonstration.

CREATE DATABASE fkdemo;
USE fkdemo;

1. RESTRICT & NO ACTION actions

Inside the fkdemo database, create two tables categories and products:

CREATE TABLE categories(
  categoryId INT AUTO_INCREMENT PRIMARY KEY, 
  categoryName VARCHAR(100) NOT NULL
) ENGINE = INNODB;


CREATE TABLE products(
  productId INT AUTO_INCREMENT PRIMARY KEY, 
  productName VARCHAR(100) NOT NULL, 
  categoryId INT, 
  CONSTRAINT fk_category 
     FOREIGN KEY (categoryId) 
     REFERENCES categories(categoryId)
) ENGINE = INNODB;

The categoryId in the products table is the foreign key column that refers to the categoryId column in the categories table.

Because we don’t specify any ON UPDATE and ON DELETE clauses, the default action is RESTRICT for both update and delete operations.

💡

The following steps illustrate the RESTRICT action.

Insert two rows into the categories table:

INSERT INTO categories
            (categoryName)
VALUES
      ('Smartphone'),
      ('Smartwatch');

Select data from the categories table:

SELECT * FROM categories;

Insert a new row into the products table:

INSERT INTO products
           (productName, categoryId)
VALUES
      ('iPhone',1);

It works because the categoryId 1 exists in the categories table.

Attempt to insert a new row into the products table with a categoryId value does not exist in the categories table: like inserting 3 whereas the id stops at number 2.

INSERT INTO products
            (productName, categoryId)
VALUES 
      ('iPad',3);

MySQL issued the following error:

Error Code: 1452. Cannot add or update a child row: a foreign key constraint fails (fkdemo.products, CONSTRAINT fk_category FOREIGN KEY (categoryId) REFERENCES categories (categoryId) ON DELETE RESTRICT ON UPDATE RESTRICT)

Update the value in the categoryId column in the categories table to 100:

UPDATE categories
SET categoryId = 100
WHERE categoryId = 1;

MySQL issued this error:

Error Code: 1451. Cannot delete or update a parent row: a foreign key constraint fails (fkdemo.products, CONSTRAINT fk_category FOREIGN KEY (categoryId) REFERENCES categories (categoryId) ON DELETE RESTRICT ON UPDATE RESTRICT)

💡

Because of the RESTRICT option, you cannot delete or update categoryId 1 since it is referenced by the productId 1 in the products table.

CASCADE action

These steps illustrate how ON UPDATE CASCADE and ON DELETE CASCADE actions work.

DROP TABLE products;

This is to restore the information back to default while we recreate the $product$ table.

Create the products table with the ON UPDATE CASCADE and ON DELETE CASCADE options for the foreign key:

CREATE TABLE products(
    productId INT AUTO_INCREMENT PRIMARY KEY,
    productName varchar(100) not null,
    categoryId INT NOT NULL,
    
    CONSTRAINT fk_category
    FOREIGN KEY (categoryId) 
    REFERENCES categories(categoryId)
        ON UPDATE CASCADE
        ON DELETE CASCADE
) ENGINE=INNODB;

Insert four rows into the products table:

INSERT INTO products
            (productName, categoryId)
VALUES
    ('iPhone', 1), 
    ('Galaxy Note',1),
    ('Apple Watch',2),
    ('Samsung Galary Watch',2);

Select data from the products table:

SELECT * FROM products;

Update categoryId 1 to 100 in the categories table:

UPDATE categories
SET categoryId = 100
WHERE categoryId = 1;

Verify the update:

SELECT * FROM products;

As you can see, two rows with value 1 in the categoryId column of the products table was automatically updated to 100 because of the ON UPDATE CASCADE action.

Delete categoryId 2 from the categories table:

DELETE FROM categories
WHERE categoryId = 2;

Verify the deletion:

SELECT * FROM categories;

Check the products table:

SELECT * FROM products;

All products with categoryId 2 from the products table was automatically deleted because of the ON DELETE CASCADE action.

SET NULL action

These steps illustrate how the ON UPDATE SET NULL and ON DELETE SET NULL actions work.

1) Drop both categories and products tables:

DROP TABLE IF EXISTS categories;
DROP TABLE IF EXISTS products;

2) Create the categories and products tables:

CREATE TABLE categories(
    categoryId INT AUTO_INCREMENT PRIMARY KEY,
    categoryName VARCHAR(100) NOT NULL
)ENGINE=INNODB;

CREATE TABLE products(
    productId INT AUTO_INCREMENT PRIMARY KEY,
    productName varchar(100) not null,
    categoryId INT,
    
    CONSTRAINT fk_category
    FOREIGN KEY (categoryId) 
        REFERENCES categories(categoryId)
        ON UPDATE SET NULL
        ON DELETE SET NULL 
)ENGINE=INNODB;

The foreign key in the products table changed to ON UPDATE SET NULL and ON DELETE SET NULL options.

3) Insert rows into the categories table:

INSERT INTO categories
            (categoryName)
VALUES
    ('Smartphone'),
    ('Smartwatch');

4) Insert rows into the products table:

INSERT INTO products
            (productName, categoryId)
VALUES
    ('iPhone', 1), 
    ('Galaxy Note',1),
    ('Apple Watch',2),
    ('Samsung Galary Watch',2);

5) Update categoryId from 1 to 100 in the categories table:

UPDATE categories
SET categoryId = 100
WHERE categoryId = 1;

6) Verify the update:

SELECT * FROM categories;

7) Select data from the products table:

The rows with the categoryId 1 in the products table was automatically set to NULL due to the ON UPDATE SET NULL action.

8) Delete the categoryId 2 from the categories table:

DELETE FROM categories 
WHERE categoryId = 2;

9) Check the products table:

SELECT * FROM products;

The values in the categoryId column of the rows with categoryId 2 in the products table was automatically set to NULL due to the ON DELETE SET NULL action.

‣

Drop MySQL foreign key constraints

‣

FOREIGN KEY

Introduction to MySQL foreign key

Self-Referencing Foreign key

MySQL FOREIGN KEY syntax

Explaining the Syntax:

Putting It All Together (Example)

Example 1

🔍 Line-by-line, in your preferred style

CREATE TABLE customers (...)

CREATE TABLE categories (...)

CREATE TABLE products (...)

CREATE TABLE orders (...)

CREATE TABLE order_items (...)

Why these ON DELETE / ON UPDATE choices?

Quick ERD (ASCII)

Mini demo (shows constraints working)

Notes & Gotchas (MySQL/InnoDB)

MySQL FOREIGN KEY examples

1. RESTRICT & NO ACTION actions

CASCADE action

SET NULL action

Drop MySQL foreign key constraints

Disabling foreign key checks

`CREATE TABLE customers (...)`

`CREATE TABLE categories (...)`

`CREATE TABLE products (...)`

`CREATE TABLE orders (...)`

`CREATE TABLE order_items (...)`

Why these `ON DELETE` / `ON UPDATE` choices?