Mastering MySQL Foreign Keys: Definitions, Cascades, and Practical Examples

Background

In many projects the author was repeatedly advised not to use foreign keys, so this note collects the essential knowledge about them.

What is a foreign key?

A foreign key in MySQL establishes a reference relationship between two tables, linking a column in the child table to a primary‑key or unique column in the parent table. It enforces referential integrity, ensuring data accuracy and reliability.

Defining a foreign key

When creating a table, the FOREIGN KEY clause is used together with REFERENCES to specify the referenced table and column.

CREATE TABLE table_name (
    column1 datatype,
    column2 datatype,
    ...,
    FOREIGN KEY (fk_column) REFERENCES parent_table(parent_column)
);

Cascade actions

Foreign keys can define cascade operations such as CASCADE, SET NULL, SET DEFAULT and NO ACTION, which determine how changes in the parent table affect rows in the child table.

CREATE TABLE parent (
    id INT PRIMARY KEY
);
CREATE TABLE child (
    col INT,
    FOREIGN KEY (col) REFERENCES parent(id) ON DELETE CASCADE
);

Restrictions

The referenced column must be a primary key or a unique index.

The data types of the foreign key column and the referenced column must match.

Only the InnoDB storage engine supports foreign keys; MyISAM does not.

Advantages and disadvantages

Advantages

Data consistency – prevents invalid references.

Data integrity – blocks accidental deletions or allows controlled cascade deletions.

Simplifies join queries.

Disadvantages

Performance overhead, especially on large tables.

Increased schema complexity.

Different DBMSs have varying support, which can cause migration issues.

Simple example (MySQL 5.7)

-- Create users table
CREATE TABLE users (
    id INT PRIMARY KEY,
    username VARCHAR(255) NOT NULL
);

-- Create user_extra table with a foreign key
CREATE TABLE user_extra (
    user_id INT PRIMARY KEY,
    remark VARCHAR(255),
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
);

Metadata query

You can query information_schema.KEY_COLUMN_USAGE to retrieve foreign‑key metadata.

SELECT TABLE_NAME,
       COLUMN_NAME,
       CONSTRAINT_NAME,
       REFERENCED_TABLE_NAME,
       REFERENCED_COLUMN_NAME
FROM   information_schema.KEY_COLUMN_USAGE
WHERE  REFERENCED_TABLE_SCHEMA = 'test';

Initialize data and test cascade delete

INSERT INTO users (id, username) VALUES (1, 'u1');
INSERT INTO user_extra (user_id, remark) VALUES (1, 'u1-ex');

-- Delete parent row; child rows are removed automatically
DELETE FROM users WHERE id = 1;

SELECT * FROM user_extra;  -- returns empty set

Conclusion

Foreign keys are convenient for guaranteeing data consistency, but they are sometimes avoided due to performance concerns, complications with horizontal sharding, or the desire to keep microservices loosely coupled. Choose the approach that best fits your specific business scenario.