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

Error Code: 1005. Can't Create Table: A Detailed Analysis

When encountering Error Code 1005 while creating a foreign key with the following script:

ALTER TABLE sira_no
    ADD CONSTRAINT METAL_KODU FOREIGN KEY(METAL_KODU)
    REFERENCES metal_kod(METAL_KODU)
    ON DELETE SET NULL
    ON UPDATE SET NULL ;

It's essential to understand the underlying reasons behind this error.

The error message, "Can't create table 'ebs.#sql-f48_1a3' (errno: 150)," typically indicates that there is an issue with the primary key reference. To resolve this, it's crucial to ensure that:

• The data types and sizes of key fields are identical:
  Ensure that the types and sizes match exactly. For instance, INT(10) and BIGINT are incompatible. Similarly, SIGNED and UNSIGNED fields should be matched accordingly.
• Relevant fields have appropriate indexes:
  Foreign key relationships require an index or primary key on the referenced field. If the referenced field lacks an index, create one to establish the constraint.
• Foreign key name is unique:
  Duplicate foreign key names can result in errors. Verify that the foreign key name is unique within the database by adding random characters to its end.
• Tables are of InnoDB type:
  Foreign key constraints require both tables involved to be InnoDB tables. MyISAM tables cannot support foreign keys.
• Cascade options and field nullability are compatible:
  If the ON DELETE or ON UPDATE actions are set to CASCADE, the referenced field cannot have a NOT NULL constraint.
• Charset and Collation options are consistent:
  Ensure that the Charset and Collation parameters are identical for both the table and the key columns in the relationship.
• Default values on foreign key columns are not present:
  Foreign key columns should not have default values defined.
• Composite keys with separate indexes are required:
  If the referencing field is part of a composite key, it must have its own index, separate from the composite index.

Thoroughly checking the listed causes and addressing any discrepancies should resolve Error Code 1005. For further details, consult MySQL's documentation on Error Number 1005: Can't create table.

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