helpkelp helpkelp - 7 months ago 46
MySQL Question

Use of Primary Key as Foreign Key in Foreign Key Table

This may sound confusing and or simple but..

If I use a foreign key from Table B in Table A, which has a separate primary key. Do I need to include Table A's primary key as a foreign key in Table B?




Okay, let me try and clarify my question a bit.


In the case above, should I use Taco_ID as a FK in Table 2? Or is does it completely unnecessary?


In general, you don't usually make foreign keys bidirectionally like that. If you do, it means that the two tables exist in a 1-to-1 relationship: Each taco has a type, and each taco type can only be used by one taco. If you have a relationship like this, there's not really any reason to have them in separate tables, they could just be additional columns in the same table.

Normally foreign keys are used for 1-to-many or many-to-many relationships. A 1-to-many relationship would be if many different tacos can be of the same type. They each have Taco_Type_ID foreign key.

For a many-to-many relationship, you typically use a separate relation table.

    Taco_ID INT, -- FK to Table1.Taco_ID
    Taco_Type_ID INT, -- FK to Table2.Taco_Type_ID
    PRIMARY KEY (Taco_ID, Taco_Type_ID)