Why Am I Getting MySQL Error 1005 (errno: 150) When Creating a Foreign Key Constraint?

Error Code: 1005. Can't create table ... (errno: 150)

In your attempt to create a foreign key constraint between tables sira_no and metal_kod, you encountered Error Code 1005, indicating an inability to create a table due to errno 150.

This error arises due to an incorrect primary key reference in your code. When defining a foreign key relationship, the referenced field in the parent table must exist, match the data type and size of the foreign key field, and have an index or be a primary key.

Troubleshooting Steps:

1. Check Field Definitions: Ensure that the METAL_KODU fields in both tables have identical data types and sizes (e.g., both should be VARCHAR(4), not VARCHAR(5)), including signed or unsigned properties. Check the case sensitivity as well.
2. Create an Index: Verify that the referenced METAL_KODU field in the metal_kod table has an index. Without an index, the foreign key constraint cannot be established.
3. Check Table Types: Both tables involved in the relationship must use the InnoDB storage engine to support foreign key constraints.
4. Inspect Foreign Key Name: Ensure that the name of your foreign key (METAL_KODU) does not duplicate an existing key.
5. Handle Null Values: If the METAL_KODU field in the parent table is set to NOT NULL and you have specified ON DELETE SET NULL in the foreign key constraint, change either the column property or the cascade behavior.
6. Charset and Collation: Check that the character set and collation settings match for both tables and the key columns.
7. Remove Default Values: If your foreign key column has a default value (e.g., default=0), remove it.
8. Check Composite Keys: If the METAL_KODU field is part of a composite key in the parent table, create a separate index for that field to support the foreign key constraint.
9. Review Syntax: Double-check your ALTER TABLE statement and ensure that the field names are typed correctly.
10. Foreign Key Length: Ensure that the name of your foreign key does not exceed the maximum length of 64 characters.

By addressing these potential issues, you can resolve the Error Code 1005 and successfully establish the foreign key relationship between the sira_no and metal_kod tables.

The above is the detailed content of Why Am I Getting MySQL Error 1005 (errno: 150) When Creating a Foreign Key Constraint?. For more information, please follow other related articles on the PHP Chinese website!