helpkelp helpkelp - 1 month ago 6
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?

Thanks!

========================================================================

EDIT:

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

EXAMPLE

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

Answer

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.

CREATE TABLE Taco_Types (
    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)
);
Comments