Primary Keys and Relationships Between Tables
We learn about the primary key that identifies a record uniquely and how linking tables helps avoid repeating data.
All topics (7 of 11)
Chapter 1. Spreadsheets
Chapter 2. Databases
Chapter 3. Computer Networks and the Web
Chapter 4. Number Systems
There can be two students with the same name, so a record cannot be identified reliably by name. A primary key is a field (or a set of fields) whose value is never repeated in any record; it identifies the record uniquely. Usually a serial number (ID), a passport number or a student code is used, and a key field must not be empty.
If we put all the information into one table, it starts to repeat. For example, if a client’s name and phone are rewritten in every order in an “Orders” table, space is wasted and mistakes are easy to make when changing them. So the data is split into several tables: a separate “Clients” table (code, name, phone) and “Orders” (number, client code, date).
To link tables, the primary key of one table is also stored in the other, where it is called a foreign key. In our example the “client code” in “Orders” is a foreign key to the “Clients” table. The relationship is “one to many”: one client can place many orders, but each order belongs to only one client.
Key takeaways
- A primary key identifies a record uniquely and is never repeated.
- Splitting data into tables reduces repetition.
- A foreign key points to the primary key of another table.
- In a “one to many” relationship, one record corresponds to several related ones.
Practice tasks
- Choose a primary key for a “Students” table (name, date of birth or ID) and justify your choice.
- Design “Books” and “Authors” tables: how will you link them so the author’s name is not repeated for every book? Write down the fields.
- Find another school example of a “one to many” relationship (for example, a class and its students) and say which table holds the foreign key.
Review questions
Test yourself: think first, then reveal the answer.
1Why can a name not be a primary key?Show answerHide answer
Because names can repeat, while a primary key must be unique.2What is a foreign key?Show answerHide answer
A field that refers to the primary key of another table.
0 of 11 topics completed
Sign in to mark