Mysql: Can't create table (errno: 150)

sql

foreign-keys

database-design

mysql-error

byNikita Barsukov·Jan 25, 2025

Errno: 150 is synonymous with a foreign key constraint failure in MySQL. You can remedy this by confirming identical data types, storage engines, and ensuring creation of the parent table before the child. Consider this example:

/* ADOPT before you adapt. Parents come first. Remember this in life and code! */
CREATE TABLE parent (id INT PRIMARY KEY) ENGINE=InnoDB;

/* MIME your way into the family. Be actual and factual */
CREATE TABLE child (pid INT, FOREIGN KEY (pid) REFERENCES parent(id)) ENGINE=InnoDB;

Make sure that pid in the child mirrors the type of id in parent, and both tables use InnoDB.

Deep Dive into Error Codes: 150 vs 1005

Error 150, a subset of the more general Error 1005, points towards an issue with creating or altering a table in MySQL. Both these errors emphasize a need for meticulous table definition.

Diagnosing and troubleshooting common causes

INNODB's X-ray Vision

Execute SHOW ENGINE INNODB STATUS; providing diagnostic insights into the issues hidden behind errno 150. This helps derive a more focused solution.

Checking Referential Integrity

Ensure that the referred columns are equipped with PRIMARY or UNIQUE keys. If they are absent, create requisite indexes. Maintaining referential integrity is crucial for roping in foreign keys without inviting errno: 150.

Matching the Matchmakers: Data Types and Collations

Foreign keys need to find their perfect match in the referred columns for data types, including those pesky differences like INT(10) vs INT(11). When dealing with VARCHAR or CHAR data types, keep a keen eye on matching collations.

Navigating the Import-Nightmare

Before launching an import, turning off checks temporarily using SET FOREIGN_KEY_CHECKS=0; feels like turning on the lights in a dark alley. Remember to turn it back on using SET FOREIGN_KEY_CHECKS=1; post-troubleshooting.

Arranging the orderly chaos

When re-creating a database, drop tables in the reverse order of creation to avoid breaching foreign key constraints. This prevents the stubble of residual referential links from triggering your new definitions into a conflict.