Can a Primary Key Also Serve as a Foreign Key, and How Do I Fix MySQL Error 1005?

MySQL Foreign Key Error 1005 errno 150: Resolving the Primary Key as Foreign Key Issue

MySQL's foreign key constraints enforce data integrity by referencing fields in other tables. However, when using a primary key as a foreign key, you may encounter error 1005. This error occurs when the referenced table lacks a usable index for the referenced columns.

Can a Primary Key be a Foreign Key?

Yes, a primary key can also be a foreign key. This is a common practice in database design, as it ensures that the referencing table contains only valid data and maintains the referential integrity of the relationships.

Resolving Error 1005

To resolve error 1005, you need to create an index on the referenced columns in the referenced table. This index allows MySQL to efficiently find the referenced records. Here's how you can resolve the issue in your specific case:

ALTER TABLE Immobile ADD PRIMARY KEY (`ComuneImmobile`, `ViaImmobile`, `CivicoImmobile`, `InternoImmobile`);

By adding the primary key, the columns will be automatically indexed. This ensures that the foreign keys in the other tables (Condoni) can reference the correct records in the Immobile table.

Alternative Solution

If creating an index on the primary key is not feasible, you can use a composite foreign key instead. This means using multiple columns from the primary key as foreign key references. In this case, you would create the following foreign keys:

ALTER TABLE Condoni ADD FOREIGN KEY (`ComuneImmobile`, `ViaImmobile`) REFERENCES Immobile (`ComuneImmobile`, `ViaImmobile`);

This composite foreign key would enforce the referential integrity without the need for an index on the primary key.

By implementing these solutions, you can establish the desired relationships between your database tables and avoid error 1005 when using a primary key as a foreign key reference.

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