jeudi 5 février 2015

How can I make a foreign key from a fragment of the primary key?


I have this table:



CREATE TABLE Phones (
Brand VARCHAR(20),
Model VARCHAR(20),
PRIMARY KEY (Brand, Model)
);


A mobile phone can't be uniquely identified just by its model (for example: HTC (One) or OnePlus (One)). Therefore, I made a composite primary key. The problem is that now I need to form a foreign key with each column, like so:



CREATE TABLE Unlocked_Phones (
Brand_Phone VARCHAR(20),
Model_Phone VARCHAR(20),
Price SMALLINT UNSIGNED NOT NULL,

CONSTRAINT FK1_Unlocked_Phones FOREIGN KEY(Brand_Phone) REFERENCES Phones(Brand),
CONSTRAINT FK2_Unlocked_Phones FOREIGN KEY(Model_Phone) REFERENCES Phones(Model)
);


Obviously, this doesn't work, because the primary key is made out of two columns. Therefore, MySQL gives me an error number 150.


Because I want to be able to browse unlocked phones by brand, rather than using a single column to reference the composite key. I thought about adding an auto-increment ID and then make a foreign key out of that, the problem with that approach is that the relationship would be killed and the database would have to search through the IDs to display each phone, going back and forth from table to table if the user wants to filter by brand, or maybe price.


What's the suggested approach here?





Aucun commentaire:

Enregistrer un commentaire