How to Fix MySQL Error 1005 When Adding Foreign Keys to Existing Tables?

Troubleshooting Foreign Key Creation in Existing Tables

When attempting to add a foreign key to a table, you may encounter the "Error Code: 1005" indicating an issue with table resolution. Let's explore the issue and provide a solution:

Origin of the Error

The error occurs because MySQL cannot resolve the table name "Sprache" when executing the ALTER TABLE statement. The table is not recognized because it is preceded by the temporary table name "mytable.#sql-7fb1_7d3a".

Solution for MySQL 5.1.61 and Below

For MySQL versions 5.1.61 and below, you can resolve the issue by using the following modified ALTER TABLE syntax:

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

In place of "Sprache", ensure the actual field name used as the foreign key is used.

Note: If you are using MySQL versions higher than 5.1.61, you may experience a different error related to the "ON DELETE SET NULL" clause. In such cases, refer to the appropriate documentation for the specific MySQL version.

The above is the detailed content of How to Fix MySQL Error 1005 When Adding Foreign Keys to Existing Tables?. For more information, please follow other related articles on the PHP Chinese website!