helpkelp helpkelp - 9 months ago 53
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?

Answer Source

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)