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

This is an interview que开发者_运维问答stion 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?

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.

The easiest way is to create a composite primary key on GRADHONS (a primary key that is composed of both GraduateID and HonoursID fields) in your join table which consists of Graduate and Honours FKs.

If you're going to use LINQ to SQL or other ORM solutions like ActiveRecord in RoR be aware that they tend to dislike these types of PKeys. In that case you would be best to instead create a PK autoincrement on the join table GRADHONS and then a unique index on the two FKey fields.