Why Am I Getting a MySQL 'Can't create table (errno: 150)' Error?

Solving MySQL "Unable to create table (errno: 150)" error

Encountering error 150 when creating a table during SQL import can be confusing. This article dives into the root cause of this issue, providing a clear understanding and solutions.

Problem Analysis

The error "MySQL: Unable to create table (errno: 150)" occurs when the definition of an imported table conflicts with a foreign key constraint referencing it. These constraints ensure data integrity between related tables.

Check foreign key constraints

To resolve this error, verify the following aspects of the foreign key constraint:

1. Are the column names and types correct: Make sure the newly created table has the same column names and data types as the columns referenced in the parent table.
2. Index exists: Verify that the table being created has an index on the column referenced by the foreign key. Lack of a suitable index breaks referential integrity and triggers Error 150.

Resolve conflicts

Once you have determined the differences between the table definition and the foreign key constraints, modify the table definition to conform to the expected constraints. This may include adding missing columns, modifying data types, or creating necessary indexes.

Other notes

• If a table is dropped and then re-created, its definition must be the same as the original table to avoid foreign key constraint conflicts.
• ALTER TABLE operations that alter a table with a foreign key constraint will also trigger Error 150 if the change affects the validity of the constraint.

The above is the detailed content of Why Am I Getting a MySQL 'Can't create table (errno: 150)' Error?. For more information, please follow other related articles on the PHP Chinese website!