Informatics Practices · Ch 6 — Database Concepts
Foreign Key
Foreign Key
A primary key identifies tuples within one relation; a foreign key is what ties two relations together. It is the mechanism through which the relational model represents relationships — the reason a database is a connected whole rather than a pile of independent tables.
What a foreign key is
A foreign key is an attribute of one relation whose values are derived from the primary key of another relation. Whenever an attribute of one relation (the referencing relation) is used to refer to contents of another (the referenced) relation, that attribute becomes a foreign key — provided it refers to the primary key of the referenced relation.
The naming that goes with this:
- The referencing relation (the one holding the foreign key) is called the foreign relation.
- The relation in which the referenced primary key is defined is called the primary relation or master relation.
In the StudentAttendance database there are two foreign keys:
- GUID in STUDENT refers to the primary key GUID of GUARDIAN — so STUDENT is the foreign relation and GUARDIAN the master relation for this link.
- RollNumber in ATTENDANCE refers to the primary key RollNumber of STUDENT — here ATTENDANCE references STUDENT.
Foreign keys and NULL
In some cases a foreign key can take a NULL value, as long as it is not part of the primary key of the foreign table. The meaning of a NULL foreign key is simply "this row does not (yet) point at any row of the master relation". In the STUDENT table's snapshot, for example, one student's GUID is blank — the student exists, but no guardian record is linked yet. Contrast this with RollNumber in ATTENDANCE: there it participates in the primary key (AttendanceDate + RollNumber), so it can never be NULL.
Reading a schema diagram
The two foreign keys of the StudentAttendance database are shown in a schema diagram, with two conventions worth memorising:
- A foreign key is displayed as a directed arc (arrow) originating at the foreign-key attribute and ending at the corresponding primary-key attribute of the referenced table.
- The underlined attributes in each relation make up that relation's primary key. …
The figure is a schema diagram of the StudentAttendance database, drawn in the compact style used for showing keys: each relation appears as a horizontal row of attribute cells rather than a full table of data. STUDENT is the row RollNumber, SName, SDateofBirth, GUID with RollNumber underlined; GUARDIAN is the row GUID, GName, GPhone, GAddress with GUID underlined; ATTENDANCE is the row AttendanceDate, RollNumber, AttendanceStatus with both AttendanceDate and RollNumber underlined. The underlining is the diagram's first convention: an underlined attribute belongs to the primary key of its table. So RollNumber uniquely identifies a student, GUID uniquely identifies a guardian, and in ATTENDANCE no single column suffices — the combination of AttendanceDate and RollNumber forms a composite primary key, since one student has many dated entries and one date covers many students.
The second convention is the pair of directed arcs marking the foreign keys. One arrow originates at GUID in STUDENT and ends at GUID in GUARDIAN; the other originates at RollNumber in ATTENDANCE and ends at RollNumber in STUDENT. An arc always starts at the foreign key and points to the corresponding primary-key attribute of the referenced table. STUDENT.GUID is a foreign key because its values are drawn from the primary key of GUARDIAN — making STUDENT the referencing (foreign) relation and GUARDIAN the primary, or master, relation for that link. Likewise ATTENDANCE.RollNumber takes its values from STUDENT's primary key. …