Keys and relations

In a real database, data does not sit in one huge table but in several linked ones. What links them are keys.

Why one table is not enough

If every student row also held the faculty name, its dean and its building, one faculty’s details would be repeated hundreds of times. Change of dean? Update hundreds of rows — and miss one, and the database starts contradicting itself.

The solution: facts about a faculty are stored once in a separate faculties table, and a group only remembers the faculty’s number (faculty_id).

Primary key

A column that tells every row apart. Usually it is id:

• it never repeats — two students never share an id;
• it is never empty;
• it never changes — a name may change, an id never does.

Foreign key

A column that points to another table’s primary key: students.group_id → study_groups.id, study_groups.faculty_id → faculties.id.

SELECT id, name, faculty_id FROM study_groups LIMIT 5;

faculty_id = 1 means the group belongs to faculty number 1. To see which faculty that is, look in the second table:

SELECT id, name FROM faculties WHERE id = 1;

A foreign key protects the database: you cannot enter the number of a faculty that does not exist, and you cannot delete a faculty that still has groups. In the Practice section you will try this with your own hands.

Kinds of relationship

• One-to-many (1:N) — the most common: one faculty has many groups, one group has many students. The foreign key sits on the “many” side.
• Many-to-many (M:N) — a student gets grades in many subjects, and many students get grades in one subject. This needs a third table in between — here it is grades (student_id + subject_id).
• One-to-one (1:1) — rare: for example, an employee and their passport details.

Reading a schema

In the Arena, every database has a schema: the lines show the foreign keys. Looking at the schema before writing a query is a good habit: what is in which table, and which column links them.

Remember

• A primary key is the row’s unique number.
• A foreign key points to a row in another table and blocks broken links.
• An M:N relationship is built with a link table.