A primary key uniquely identifies each record in a table, a foreign key links one table to another, and a composite key uses two or more attributes together to identify a record uniquely.
How Each Key Works
In a relational database, data is organized into tables containing records (rows) and attributes (columns). Keys establish identity and relationships within this structure.
| Key type | Purpose | Example |
|---|---|---|
| Primary key | Uniquely identifies every record in its table | StudentID in a Student table |
| Foreign key | References a primary key, or another candidate key, in a related table | StudentID in an Enrollment table |
| Composite key | Combines two or more attributes whose values together are unique | StudentID and CourseID in Enrollment |
A primary key must contain unique values and cannot contain NULL. For example, two students may have the same name, but they cannot have the same StudentID.
A foreign key creates a relationship between tables and supports referential integrity. If Enrollment.StudentID references Student.StudentID, every non-null student identifier in Enrollment must correspond to an existing student, unless the database's integrity rules permit another defined action.
Suppose the Enrollment table contains:
| StudentID | CourseID | Grade |
|---|---|---|
| S01 | CS01 | 6 |
| S01 | MA01 | 7 |
| S02 | CS01 | 5 |
Neither StudentID nor CourseID is unique by itself. However, the combination (StudentID, CourseID) uniquely identifies each enrollment, so these attributes can form a composite primary key.
A common misconception is that a composite key is completely separate from a primary key. In fact, a primary key may be simple (one attribute) or composite (multiple attributes). An attribute can also participate in both a composite primary key and a foreign-key relationship.
Exam Technique
For an IB Computer Science question using identify, state each key's role. For explain, connect the key to uniqueness, table relationships, or referential integrity and apply it to the given database schema. Do not claim that foreign-key values must be unique: repeated foreign-key values are normally allowed.
