Why Am I Getting MySQL Error 1005: Can't Create Table?

Can't Create Table Error: Troubleshooting "Error Code: 1005. Can't create table '...'"

This error in MySQL occurs when an attempt is made to create a table but encounters an issue, denoted by the error code 1005. The specific problem relates to foreign key referencing.

Potential Causes:

• Mismatched Key Fields: Ensure that the type and size of the primary key field in the referenced table match exactly that of the foreign key field. Check for discrepancies in size (e.g., INT vs. BIGINT) or signed/unsigned status.
• Missing Index or Non-Primary Key: Create an index on the referenced key field if it doesn't have one or is not a primary key.
• Duplicate Foreign Key Name: Verify that the foreign key name is unique within the database. Add random characters to the end of the key name to test this.
• Non-InnoDB Table Type: Foreign key relationships require both tables to be of the InnoDB type. Convert MyISAM tables to InnoDB.
• NULL Constraint Conflict: Ensure that the key field being referenced doesn't have a NOT NULL constraint while using cascade ON DELETE SET NULL. Allow NULL values if necessary.
• Charset/Collation Mismatch: Check that both tables and their key columns use the same character set and collation settings.
• Default Value on Foreign Key: Remove any default value set on the foreign key column, as this can interfere with referencing.
• Composite Key Without Individual Index: Create a separate index for each key field used in a composite key, even if it's already indexed as part of the composite.
• Incorrect Syntax or Mistyped Field Names: Verify the syntax of your ALTER statement and check for typos in the field names involved in the relationship.
• Foreign Key Name Length Exceeds Limit: The foreign key name should be within the 64-character limit.

For further details and potential solutions, refer to the MySQL documentation: MySQL Error Number 1005 Can’t create table.

The above is the detailed content of Why Am I Getting MySQL Error 1005: Can't Create Table?. For more information, please follow other related articles on the PHP Chinese website!