Composite Primary Key

Sometimes a relation offers no single attribute that can tell all its tuples apart — every individual column contains repeated values. The relational model's answer is to widen the key: take more than one attribute together and use the combination as the primary key. A primary key made of more than one attribute is called a composite primary key.

The ATTENDANCE relation is the standard illustration, and it is worth working through both failures before seeing the fix:

• RollNumber alone cannot be the primary key. Attendance is recorded every day, so the same student's roll number appears again in another row for a different date. The value 3, for example, occurs once for each date on which student 3 was marked.
• AttendanceDate alone cannot be the primary key either. On any given date, attendance is marked for every student, so the same date is repeated across the rows — once for each roll number.

Each attribute repeats; neither can single out one row. But look at the two together. A row of ATTENDANCE answers the question "was this student present on this date?" — and on any working day a student's attendance is marked only once. So the pair of values (RollNumber, AttendanceDate) never repeats: the combination is unique in every tuple. The set {RollNumber, AttendanceDate} therefore serves as the composite primary key of the ATTENDANCE relation.