Foreign keys
A schema is a set of database tables that together make up the model of the subject area. For example, a university database is likely to have tables to represent students, modules and programmes. Each table represents a set of entities of the same type, and can potentially contain thousands of rows each of which represents a single entity. In order to locate just the rows that we are interested in, each table needs a primary key. Recall that a primary key is an attribute or group of attributes which uniquely identifies an entity. A primary key which is made up of several attributes (columns) is known as a composite primary key.
So far, so good. Now let's use a familiar example to work out how to represent relationships. Take a look at the ER diagram below.
This diagram says that one PROGRAMME is populated by many students. The multiplicity labels tell us that a STUDENT must be associated with a PROGRAMME, but that a PROGRAMME can exist without any students (think of a new programme that has not yet recruited).
Put another way, a group of records in the STUDENT table are related to a unique record in the PROGRAMME table: this sounds like something we can use the primary key for. If we attach the primary key from the PROGRAMME record to the related STUDENT records, that will accurately represent the relationship. That is exactly what happens, but before looking at the way it is done, let's think quickly about the columns that these two tables might have.
This diagram extends the basic form of an entity in an ER diagram to include the entity's attributes. The PROGRAMME table contains the programme code and title, and the programme code is used as a primary key. Notice that it is labelled in the diagram. The STUDENT table contains the personal details of the students along with some administrative information to help the university operate. Each STUDENT has a matriculation number, for example, and a status code. Not surprisingly, the matriculation number is used as the STUDENT primary key.
To associate a group of records in the STUDENT table with a row in the PROGRAMME table, we need a place to put the primary key from the PROGRAMME table. The only way to store this kind of information in a table is as a column; therefore we need to add a new column to the STUDENT table as shown below.
Now for every STUDENT, we can store the programme code for the corresponding PROGRAMME record. The new column is imported from another table where it is the primary key. In the STUDENT table, it is called a foreign key, and its job is to identify a unique record in another table.
Looking at some example data can help make this explanation clearer. Let's assume that there are three students on the Flat-Pack Design programme, and three on the Fast Food Management programme. The data might look like this:
PROGRAMME
| prog_code | prog_title |
|---|---|
| MCD | Fast Food Management |
| IK | Flat-Pack Design |
STUDENT
| matric_no | first_name | last_name | date_of_birth | status | prog_code |
|---|---|---|---|---|---|
| 1234 | Anne | Archer | 12/4/90 | C | MCD |
| 1235 | Bob | Barber | 20/3/91 | C | IK |
| 1236 | Carole | Cook | 7/7/92 | C | IK |
| 1237 | David | Driver | 1/12/91 | M | MCD |
| 1238 | Erica | Eggler | 23/3/90 | C | MCD |
| 1239 | Fred | Fletcher | 17/5/91 | C | IK |
From the values in the foreign key column, it is clear which of the six students is on which programme.
Remember:
- A foreign key always corresponds to a primary key in another table
- The foreign key is always at the "many" end of a relationship