Mysql: ERROR 1215 (HY000): Cannot add foreign key constraint

sql

foreign-key-constraints

data-integrity

database-performance

byAnton Shumikhin·Nov 21, 2024

The ERROR 1215 (HY000) occurs when foreign key and referenced columns are incompatible. Ensuring exact type match, including signedness, and correct composite key referencing can fix the issue. Here's an example to adjust child_column and match parent_column's type and attributes:

-- Increasing the "compatibility level" of columns: SQL edition
ALTER TABLE child_table 
MODIFY child_column INT UNSIGNED; 

-- Establishing relations with parental supervision:
ALTER TABLE child_table 
ADD CONSTRAINT fk_name 
FOREIGN KEY (child_column) 
REFERENCES parent_table(parent_column);

Aligning data types and attributes

Verify that the InnoDB table engine runs both of your tables that you desire to link with a foreign key:

-- "InnnoDB or not InnoDB, that is the question."
SHOW TABLE STATUS LIKE 'table_name';

For an incompatible table engine, an ALTER TABLE operation can convert it to InnoDB:

-- Knock Knock. Who's there? InnoDB.
ALTER TABLE course ENGINE = InnoDB;

Understand the importance of indexing

Ensure that your foreign key column is indexed. Indexing not only allows for improved performance, but also promotes optimal data integrity.

-- Welcome to the Index. Please fasten your seatbelt.
CREATE INDEX fk_index ON course(foreign_key_column);

Leverage built-in tools for debugging

Say hello to the highly helpful command SHOW ENGINE INNODB STATUS. Sift through the output focusing primarily on the LATEST FOREIGN KEY ERROR section:

-- Spoiler: try to find the hidden easter egg in the log
SHOW ENGINE INNODB STATUS;

It's essential to cleanse any data discrepancies that contradict new foreign key constraints. A thorough data cleanup might be in order before you add a foreign key.

Always stay unique -- even in foreign key names! A good practice is to name your constraints uniquely across the globe to avoid any clashes and confusion.

Ensuring column and table compatibility

Double-checking can prevent errors. Double-check again to confirm there's no data type mismatch, including considerations for string collations, and unsigned attributes. Here is a command to investigate the exact column specification:

-- "Show me what you got, Darling..."
SHOW CREATE TABLE department;

Just as in alphabet song, order matters! Make sure that your SQL script creates tables in a reasonable sequence to prevent foreign-key-to-nowhere scenarios.

explain-codes / Sql / Mysql: ERROR 1215 (HY000): Cannot add foreign key constraint

Linked

Mysql Cannot Add Foreign Key Constraint



How to store MySQL query results in another Table?



Insert statement conflicted with the FOREIGN KEY constraint - SQL Server



Postgresql Foreign Key syntax



Fastest way to copy a table in mysql?

