How to Fix MySQL Error 150: Foreign Key Mismatch?

Troubleshooting MySQL Error 150: Foreign Key Mismatch

When attempting to create a foreign key constraint in MySQL, encounters can arise. One such error is Error 150, which occurs when there is a mismatch between the foreign key definition and the referenced table.

Error Description

Error Message:

ERROR 1005 (HY000): Can't create table './test/bar.frm' (errno: 150)

Possible Cause

As documented in the MySQL documentation on foreign key constraints, foreign key references require strict adherence to specific rules. One crucial requirement is that both the referencing table and the referenced table must be InnoDB tables.

Both tables must be InnoDB tables and they must not be TEMPORARY tables.

Solution

To resolve this error, verify that both the referencing table (bar) and the referenced table (foo) are defined as InnoDB tables. If they are not, alter them to use the InnoDB engine.

ALTER TABLE bar ENGINE=InnoDB;
ALTER TABLE foo ENGINE=InnoDB;

After making this change, attempt to recreate the foreign key constraint between the two tables. If the problem persists, consult the MySQL documentation again or seek assistance on relevant forums or from MySQL experts.

The above is the detailed content of How to Fix MySQL Error 150: Foreign Key Mismatch?. For more information, please follow other related articles on the PHP Chinese website!