Why Am I Getting a 'Can't Create Table (errno: 150)' Error in MySQL When Importing a .sql File?

MySQL Error: "Can't Create Table (errno: 150)" - Troubleshooting Guide

Issue:

Importing a SQL file containing table definitions fails during table creation, resulting in the error "Can't create table './dbname/data.frm' (errno: 150)."

Root Cause:

This error, as detailed in the MySQL FOREIGN KEY Constraints documentation, arises when a table's recreation conflicts with existing foreign key constraints referencing it. The recreated table must precisely match the original in terms of:

• Column Names and Data Types: Exact correspondence is crucial.
• Indexes on Referenced Keys: All necessary indexes on columns involved in foreign key relationships must be present.

Resolution:

To resolve this, meticulously verify the following:

• Column Consistency: Confirm that the column names and data types in your SQL file's table definition are identical to those in the table referenced by the foreign key constraint. Even minor discrepancies (e.g., different character sets) can trigger this error.
• Index Integrity: Recreate any indexes on the columns involved in foreign key relationships. Ensure these indexes exist and match the original table's index specifications.

Once you've confirmed these aspects, retry the SQL import. If the problem persists, carefully examine the CREATE TABLE statement within your SQL file for any inconsistencies.

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