MySQL Error 150 & 1005: Why Can\'t I Create Foreign Key Relationships?

MySql Foreign Key Error 150: A Puzzling Enigma

When trying to create the "foo" and "bar" tables with foreign key relations, you encounter error 1005 (HY000) with reference to error 150. This error can be frustrating, leaving you perplexed as to the root cause.

According to the MySQL documentation on FOREIGN KEY Constraints, the problem arises when re-creating a dropped table that has foreign key references. The table must conform to the foreign key constraints by having matching column names and types, as well as indexes on the referenced keys. If these conditions are not met, MySQL returns error 1005, with an underlying error 150.

It's likely that your error is due to the fact that the "foo" table is not defined as an InnoDB table. The MySQL documentation explicitly states that both tables must be InnoDB tables and non-temporary.

By modifying the "foo" table creation query to specify the InnoDB engine:

mysql> CREATE TABLE foo(id INT PRIMARY KEY) ENGINE=InnoDB;

You should be able to successfully create the "bar" table with the foreign key constraint, resolving the error 150.

The above is the detailed content of MySQL Error 150 & 1005: Why Can\'t I Create Foreign Key Relationships?. For more information, please follow other related articles on the PHP Chinese website!