MySQL Error 1005: Can a Primary Key Be a Foreign Key, and How Do I Fix Foreign Key Constraint Issues?

MySQL Foreign Key Error 1005: Primary Key as Foreign Key

Can a Primary Key Also Be a Foreign Key?

No, it is not possible to define a primary key as a foreign key in MySQL. A primary key uniquely identifies records within a table, while a foreign key references records in another table. Combining these two roles would result in circular constraints.

Error Code 1005: Unable to Create Table

The error encountered, "Error 1005: Can't create table 'dbimmobili.condoni' (errno: 150)," indicates that MySQL cannot create the table due to an issue with the foreign key constraints.

Missing Index on Referenced Table

The message "Error in foreign key constraint of table dbimmobili/valutazionimercato" suggests that a missing index is causing the problem. MySQL requires that the referenced table have an index covering the columns specified in the foreign key.

Solution

Create an index on the referenced table (dbimmobili.Immobile) for the columns (ComuneImmobile, ViaImmobile, CivicoImmobile, InternoImmobile):

CREATE INDEX ix_ComuneViaCivicoInterno ON dbimmobili.Immobile (ComuneImmobile, ViaImmobile, CivicoImmobile, InternoImmobile);

This index will ensure that MySQL can efficiently find matching records in the dbimmobili.Immobile table when the dbimmobili.condoni table references them.

The above is the detailed content of MySQL Error 1005: Can a Primary Key Be a Foreign Key, and How Do I Fix Foreign Key Constraint Issues?. For more information, please follow other related articles on the PHP Chinese website!