Why am I getting MySQL Foreign Key Error 1005 when creating a foreign key referencing a primary key?

MySQL Foreign Key Error 1005: Primary Key as Foreign Key

Creating a foreign key that references a primary key is generally not recommended, as it can lead to performance issues and data integrity problems.

Primary Key as a Foreign Key

While it is possible to create a primary key that is also a foreign key, it is not a common practice. By doing so, you are limiting the flexibility of the foreign key table, as it cannot be referenced by other tables.

Error 1005

The error you are encountering, "Error 1005: Can't create table 'dbimmobili.condoni' (errno: 150)," indicates that MySQL cannot create the foreign keys correctly.

Missing Index

As stated in the error message, MySQL requires a usable index on both the referencing and referenced tables for foreign key constraints. In your case, it appears that you have not created an index on the referenced table's primary key.

Solution

To resolve the issue, ensure that the referenced table, dbimmobili.Immobile, has an index created on the primary key columns: ComuneImmobile, ViaImmobile, CivicoImmobile, and InternoImmobile. Once you have created the index, you should be able to export the changes successfully.

The above is the detailed content of Why am I getting MySQL Foreign Key Error 1005 when creating a foreign key referencing a primary key?. For more information, please follow other related articles on the PHP Chinese website!