When and How to Use Foreign Keys in MySQL: Advantages, Disadvantages, and Practical Scenarios

Foreign keys are designed to ensure consistency and integrity of logically related tables at the database level.

Many enterprise coding standards avoid foreign keys, but they have both pros and cons; they are not suitable for every scenario. The following sections explore when foreign keys are appropriate.

1. Advantages of Foreign Keys

Reduce data redundancy and simplify related data management.

Lower application code complexity by handling referential integrity in the database.

Improve documentation readability, especially when drawing ER diagrams.

Disadvantages of Foreign Keys

Performance overhead: cascading updates/deletes require checks on all related tables, which can be costly for large datasets.

Reduced flexibility for other schema changes.

2. Using Foreign Keys

Reference actions supported by MySQL:

CASCADE – child rows follow parent updates/deletes.

SET NULL – child foreign key set to NULL on parent change.

RESTRICT / NO ACTION – default, prevents parent modification.

SET DEFAULT – behaves like RESTRICT in current MySQL versions.

Example tables (InnoDB engine):

-- Parent table
CREATE TABLE f1(
  id INT PRIMARY KEY,
  r1 INT,
  r2 INT,
  r3 INT,
  KEY idx_r1(r1),
  KEY idx_u1(r2,r3)
);

-- Child table with foreign key (cascade on update)
CREATE TABLE f2(
  id INT PRIMARY KEY,
  f1_r1 INT,
  mark INT,
  CONSTRAINT f1_fk_r1 FOREIGN KEY (f1_r1) REFERENCES f1(r1) ON UPDATE CASCADE
);

-- Child table with SET NULL on update
CREATE TABLE f3(
  id INT PRIMARY KEY,
  f1_id INT,
  FOREIGN KEY (f1_id) REFERENCES f1(id) ON UPDATE SET NULL
);

-- Child table referencing multiple columns
CREATE TABLE f6(
  id INT AUTO_INCREMENT PRIMARY KEY,
  f1_r2 INT,
  f1_r3 INT,
  FOREIGN KEY (f1_r2,f1_r3) REFERENCES f1(r2,r3)
);

Scenario 1 – Strong data consistency, low concurrency

Insert a row into the parent and child tables, then update the parent. The child rows cascade correctly, demonstrating the integrity benefit.

INSERT INTO f1 VALUES (1,10,100,1000);
INSERT INTO f2 VALUES (1,1);
INSERT INTO f6 VALUES (1,100,1000);
UPDATE f1 SET id = 2 WHERE id = 1; -- cascades to f2 and f6
SELECT * FROM f2; -- shows updated foreign key value

Scenario 2 – Bulk data loading with strict consistency

Loading 4 million rows with and without foreign keys shows similar import times. Disabling foreign_key_checks can further reduce load time.

-- With foreign keys (32.57 sec)
LOAD DATA INFILE '/var/lib/mysql-files/f1_sub.dat' INTO TABLE f6;

-- Without foreign keys (25.95 sec)
LOAD DATA INFILE '/var/lib/mysql-files/f1_sub.dat' INTO TABLE f6_no_fk;

-- Disable checks, load, then re‑enable
SET foreign_key_checks=0;
LOAD DATA INFILE '/var/lib/mysql-files/f1_sub.dat' INTO TABLE f6; -- 28.42 sec
SET foreign_key_checks=1;

Scenario 3 – Transactional batch inserts

A stored procedure inserts 1 000 000 rows, committing every 500 rows.

DELIMITER $$
CREATE PROCEDURE sp_generate_data(IN tb_name VARCHAR(64), IN f_number INT)
BEGIN
  DECLARE i INT DEFAULT 0;
  SET @@autocommit=0;
  WHILE i < f_number DO
    SET @stmt = CONCAT('INSERT INTO ',tb_name,'(f1_r2,f1_r3) VALUES (CEIL(RAND()*10),CEIL(RAND()*10))');
    PREPARE s1 FROM @stmt;
    EXECUTE s1;
    SET i = i + 1;
    IF MOD(i,500)=0 THEN COMMIT; END IF;
  END WHILE;
  DROP PREPARE s1;
  COMMIT;
  SET @@autocommit=1;
END$$
DELIMITER ;

Running the procedure on a table with foreign keys takes about 1 min 14 sec, while the same operation on a table without foreign keys takes about 1 min 8 sec, showing only a modest difference.

Scenario 4 – Changing referenced column types

Altering a referenced column to a larger type fails while the foreign key exists; the constraint must be dropped first.

ALTER TABLE f1 CHANGE r2 r2 BIGINT; -- ERROR 3780
ALTER TABLE f6 DROP CONSTRAINT f6_ibfk_1;
ALTER TABLE f1 CHANGE r2 r2 BIGINT; -- succeeds
ALTER TABLE f6 ADD FOREIGN KEY (f1_r2,f1_r3) REFERENCES f1(r2,r3);

Scenario 5 – Triggers on child tables

Foreign‑key cascades do not fire triggers on child tables.

CREATE TRIGGER tr_af_update BEFORE UPDATE ON f2
FOR EACH ROW SET NEW.mark = NEW.f1_r1;

INSERT INTO f2 VALUES (1,10,5);
UPDATE f1 SET r1 = 2 WHERE r1 = 10; -- child row updates via cascade, trigger not executed
SELECT * FROM f2; -- mark remains unchanged by trigger

Scenario 6 – Partitioned parent tables

MySQL does not support foreign keys referencing partitioned tables.

CREATE TABLE f1_partition LIKE f1;
ALTER TABLE f1_partition PARTITION BY KEY() PARTITIONS 4;
CREATE TABLE f7(
  id INT PRIMARY KEY,
  f1_partition_id INT,
  FOREIGN KEY (f1_partition_id) REFERENCES f1_partition(id)
); -- ERROR 1506

Scenario 7 – High‑concurrency updates

Updating a parent row with many child rows acquires many locks, which can hurt TPS in high‑throughput environments.

BEGIN;
UPDATE f1 SET r2 = 101 WHERE r2 = 100; -- acquires 11 locks (5 on parent, 6 on child)
SELECT COUNT(*) FROM performance_schema.data_locks WHERE thread_id = 47; -- shows lock count

Foreign‑Key Limitations

Supported only by InnoDB and NDB engines.

Cannot reference virtual columns or temporary tables.

Referencing and referenced columns must share type, charset, and collation.

Both columns must be indexed.

Multi‑column foreign keys require identical column order.

Large object (BLOB/TEXT) columns cannot be referenced.

Constraint names must be unique within a database.

Cascading updates do not fire child‑table triggers.

Foreign keys are not supported on partitioned tables.

Conclusion

The article demonstrates, through several concrete examples, when foreign keys are beneficial and when they may be unsuitable. Scenarios 1, 2, and 3 are good candidates for foreign keys, while scenarios 4, 5, 6, and 7 are better handled without them, possibly by implementing referential logic in application code.