SQL ET POSTGRE 3.4 : Primary and foreign keys

In this video we look at primary and foreign keys. A primary key is a column that uniquely identifies a row of data in a table. Its values must be unique, they cannot be NULL (no missing data allowed), and a table can have only one primary key. Adding a primary key is technically optional, but having one in every table is a strongly recommended best practice.

Picking the primary key

Imagine an owners table holding information about people who own a pet, with five columns: id, first name, last name, age and city of residence. We need to decide which column will be the primary key. Remember the constraints: unique and never NULL. It would not make sense to use the first name or last name, because several people might share the same name. The same applies to age or city — these values can repeat. The id column, which contains an exclusively unique value for each row, is therefore the most appropriate choice for the primary key.
  • Primary key: unique, not null, one per table, identifies a row.
  • Foreign key: references a primary key from another table.
  • A table can have several foreign keys (or none at all).
Foreign keys are used to link two tables together. A foreign key is a column whose values match those of the primary key in another table — effectively creating a relationship between the data of the two tables. The table that holds the primary key is called the parent or referenced table, and the one with the foreign key is the child table. A child table can have several foreign keys, and it is also totally fine for a table to have none. Take a pets table that belongs to the owners above: an owner_id column points to the id of the owners table — so the cat Moustache with owner_id 3 is linked back to Audrey Richard in Lille. This is how we link tables together in a relational database, and we will use these relationships throughout the rest of the course.

Summary

This lesson explains primary and foreign keys in relational databases. A primary key is a unique, non-nullable column that uniquely identifies each row in a table (limited to one per table). Foreign keys are columns that reference a primary key from another table, establishing relationships between parent and child tables. The lesson uses a practical example with owners and animals tables to demonstrate how these keys work together.

Key points

  • Primary keys must be unique, non-nullable, and contain no missing data; only one primary key is allowed per table
  • Choosing the right primary key column requires ensuring uniqueness—unsuitable columns include those with duplicate values like names or cities
  • Foreign keys create links between two tables by storing values that correspond to another table's primary key
  • The table containing the primary key is called the parent or referenced table; the table containing the foreign key is the child table
  • A single table can contain multiple foreign keys, each linking to different parent tables
  • In the example, the ID column serves as the primary key in the owners table, while owner_id in the animals table is a foreign key linking each animal to its owner

FAQ

Why should you use ID instead of name as a primary key?

Names are not unique—multiple people can share the same name (e.g., multiple Marions). ID is specifically designed to be unique for each row, making it the appropriate choice for a primary key.

Can a table have multiple primary keys?

No, a table can have only one primary key. However, a table can have multiple foreign keys linking to different parent tables.

How do foreign keys establish relationships in a relational database?

A foreign key is a column whose values correspond to the primary key values of another table, creating a relationship that links data between the two tables together.