Computer Science · Ch 8 — Database Concepts
Foreign Key
Foreign Key
Foreign Key
A foreign key is the mechanism that represents relationships between two tables in a relational database. Without foreign keys, tables would remain isolated and you could not connect related data across them.
The core idea is simple: a foreign key in one table takes its values from the primary key of another table. The table that contains the foreign key is called the referencing relation or foreign relation. The table whose primary key is being referred to is called the referenced relation, primary relation, or master relation.
For example, in a Student Attendance database, the ATTENDANCE table would have a RollNumber column. This RollNumber is a foreign key because its values come from the RollNumber primary key in the STUDENT table. The ATTENDANCE table is the foreign relation, and the STUDENT table is the primary relation.
A foreign key can contain NULL values, but only if it is not part of the primary key of its own table. This makes sense: if a student's attendance record has no roll number yet (perhaps the student hasn't been assigned one), the foreign key can be NULL. But if the foreign key were also part of the table's own primary key, it could never be NULL because primary keys must be unique and non-null.
In schema diagrams, a foreign key is shown as a directed arc (an arrow) that originates from the foreign key attribute and points to the primary key attribute of the referenced table. The underlined attributes in such diagrams indicate the primary key of each table.
The textbook illustrates this with the STUDENTATTENDANCE database, which has three tables:
STUDENTwith attributesRollNumber,SName,SDateofBirth,GUIDGUARDIANwith attributesGUID,GName,GPhone,GAddressATTENDANCEwith attributesAttendanceDate,RollNumber,AttendanceStatus…
Drawn by us to help you understand the concept clearly, and verified to make sure it's accurate. For exams, practice from your NCERT textbook's own diagram.
This brings together everything from Figures 8.1 and 8.3, now marking which columns are the primary keys (shown italic and underlined) and drawing the foreign key relationships as arrows.
RollNumber is underlined in STUDENT because it uniquely identifies each student — that's STUDENT's primary key. GUID is underlined in GUARDIAN for the same reason. In ATTENDANCE, BOTH AttendanceDate and RollNumber are underlined together — neither one alone is unique (many students share the same date, and one student has many dates), but the combination of the two together uniquely identifies one attendance record. That combination is called a composite key. …