MySQL Foreign Key Error 1005: How to Solve Primary Key Foreign Key Conflicts?

MySQL Primary Key as Foreign Key: Error and Solution

When defining a foreign key constraint in MySQL, it's essential to ensure the referencing table and the referenced table have appropriate indexes. This requirement can cause issues when using the primary key of one table as a foreign key in another table.

Primary Key as Foreign Key

The first question you posed is if it's possible to create a primary key that is also a foreign key. The answer is yes, it's possible, but there are limitations.

Error Code 1005

The error you encountered, error code 1005, suggests that MySQL cannot create the foreign key constraint because the referenced table, dbimmobili.Immobile, does not have an index on the columns referenced in the foreign key constraint.

Solution

To resolve this issue, you need to create an index on the ComuneImmobile, ViaImmobile, CivicoImmobile, and InternoImmobile columns in the dbimmobili.Immobile table. This index will ensure that MySQL can efficiently find the referenced records when performing foreign key checks.

Once you've created the index, you should be able to export the changes without encountering the error.

Additional Notes

It's important to note that you may also encounter issues if the data types of the columns used in the foreign key constraint differ between the referencing and referenced tables. Make sure that the data types are compatible to avoid any potential errors.

The above is the detailed content of MySQL Foreign Key Error 1005: How to Solve Primary Key Foreign Key Conflicts?. For more information, please follow other related articles on the PHP Chinese website!