Skip to content

Instantly share code, notes, and snippets.

Embed
What would you like to do?
Relationships and foreign keys in SQL databases

I'll be using an Ecommerce application in these examples.

PK stands for Primary Key, and FK stands for Foreign Key.


One-to-one

Use a foreign key on both sides of the relationship.

One customer can have one address.

customer table:

id (PK) first_name last_name address_id (FK)
* * * *

address table:

id (PK) street_address city zipcode customer_id (FK)
* * * * *

You must also put a unique constraint on the foreign key columns to prevent multiple rows in the child table (address) from relating to the same row in the referenced table (customer).

One-to-many

Use a foreign key on the "many" (child) side of the relationship linking back to the "one" (parent) side.

One customer can have many orders.

customer table (parent):

id (PK) first_name last_name
* * *

order table (child):

id (PK) delivery_date customer_id (FK)
* * *

One-to-many (Lookup table)

When the child is just one value it can be stored directly in the parent table as an attribute, such as customer membership type.

But since there several set values to choose from, it would be better to put this information in a separate table called a lookup table, so that if a particular membership was renamed only one value in the lookup table would have to be updated.

customer table:

id (PK) first_name last_name membership_id (FK)
* * * *

membership table:

id (PK) name
* *

Many-to-many

Use a junction table (also called an intermediary table, a bridge table or an associative entity).

Many customers can wishlist many products.

customer table:

id (PK) first_name last_name
* * *

product table:

id (PK) name price
* * *

wishlist junction/intermediary/bridge table:

id (PK) customer_id (FK) product_id (FK)
* * *
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment