Search code examples
constraintsdatamodel

How to define a unique-relationship in a bridge table so it is enforced by the datamodel?

This is an interview question I came across

Given THREE tables

  • GRADUATE - Graduate roll
  • HONOURS - Honours Subjects
  • GRADHONS - Bridge table to link Graduate with a particular Honours subject

The question - How should GRADHONS be defined in the datamodel so the datamodel enforces the constraint that a graduate may only have a single Honours subject?

The interviewer was not terribly enamoured of my suggestions

  • Use just the Graduate Key as PK in the table GRADHONS
  • Change the datamodel and reference Honours as part of the Graduate table.

My question is the same as the original interview question - How would you define a unique-1-1 relationship in a bridge table so it is enforced by the datamodel?

Solution

  • Both your suggestions sound reasonable. If you'd prefer to have a separate column in GRADHONS to reference GRADUATE, then make GRADHONS have a foreign key reference to GRADUATE, and make that column unique. Therefore there can only be a single row in GRADHONS for any GRADUATE.