Why Am I Getting 'Error Code: 1005' When Adding a Foreign Key to My MySQL Table?

Troubleshooting Foreign Key Addition to Table

When attempting to add a foreign key to an existing table named "katalog", an error message may be encountered: "Error Code: 1005. Can't create table 'mytable.#sql-7fb1_7d3a' (errno: 150)."

Causes:

• Syntax error in the ALTER TABLE statement: The provided syntax includes an incorrect reference to the table mytable.#sql-7fb1_7d3a instead of katalog.
• Missing or invalid reference to the referenced table: Ensure that the referenced table (Sprache in this case) exists and has an appropriately matching column (ID in this case).
• Incompatibility between table and reference column attributes: Verify that the data types and constraints of the foreign key column and the referenced column are compatible.

Resolution:

1. Correct the syntax in the ALTER TABLE statement to reference the correct table name, katalog.
2. Ensure that the referenced table, Sprache, exists with an ID column that matches the foreign key constraint.
3. Verify that the data types and constraints of the foreign key column (Sprache in this case) match those of the referenced column (ID in Sprache) in the referenced table.

Example:

To successfully add the foreign key, use the following corrected ALTER TABLE statement:

ALTER TABLE katalog
ADD CONSTRAINT `fk_katalog_sprache`
FOREIGN KEY (`Sprache`)
REFERENCES `Sprache` (`ID`)
ON DELETE SET NULL
ON UPDATE SET NULL;

The above is the detailed content of Why Am I Getting 'Error Code: 1005' When Adding a Foreign Key to My MySQL Table?. For more information, please follow other related articles on the PHP Chinese website!