Understanding Foreign Keys: Syntax, Operations, Advantages, Disadvantages, and Usage Scenarios

This guide introduces foreign keys, describing their purpose and how to define them when creating a table.

-- Create table with foreign key constraint
CREATE TABLE table_name(
    column_name data_type,
    ...,
    [CONSTRAINT constraint_name] FOREIGN KEY (foreign_key_column)
        REFERENCES parent_table(parent_column)
);

It also shows how to add a foreign key to an existing table and how to remove a foreign key constraint.

-- Add foreign key after table creation
ALTER TABLE table_name ADD CONSTRAINT fk_name FOREIGN KEY (fk_column) REFERENCES parent_table(parent_column);

-- Drop foreign key constraint
ALTER TABLE table_name DROP FOREIGN KEY fk_name;

The article provides a complete exercise that creates two tables ( dept and emp ), inserts sample data, and demonstrates the effect of foreign key enforcement when deleting records.

-- Create department table
CREATE TABLE dept(
    id int PRIMARY KEY AUTO_INCREMENT,
    dep_name varchar(20),
    addr varchar(20)
);

-- Create employee table with foreign key to dept
CREATE TABLE emp(
    id int PRIMARY KEY AUTO_INCREMENT,
    name varchar(20),
    age int,
    dep_id int,
    CONSTRAINT fk_emp_dept FOREIGN KEY (dep_id) REFERENCES dept(id)
);

-- Insert sample data
INSERT INTO dept(dep_name,addr) VALUES ('研发部','广州'),('销售部','深圳');
INSERT INTO emp(name,age,dep_id) VALUES
    ('张三',20,1),('李四',20,1),('王五',20,1),
    ('赵六',20,2),('孙七',22,2),('周八',18,2);

-- Attempt to delete a department with dependent employees (fails)
DELETE FROM dept WHERE id = 1;

-- Drop the foreign key and delete successfully
ALTER TABLE emp DROP FOREIGN KEY fk_emp_dept;
DELETE FROM dept WHERE id = 1;

After the practical part, the article lists the disadvantages of foreign keys, such as the need to consider constraints during UPDATE/DELETE, potential table‑level locking that hurts concurrency, cascade delete risks, and high coupling that complicates migrations.

It then outlines the advantages, mainly ensuring data consistency and improving ER diagram readability.

Finally, the guide discusses suitable scenarios: high‑concurrency, large‑scale distributed projects often disable foreign keys for performance, handling consistency at the application layer; whereas smaller, single‑service projects with strict data integrity requirements benefit from using foreign keys.