0

I want to know if it's possible to have an unique association?

Example : A participant has an ID (Primary Key) and can be a swimmer or a runner but can't be both.

So I want to have 1 participant linked by the ID to a swimmer or a runner not both.

Thank you.

Gum Bum
  • 1
  • Is this what you are looking for? https://stackoverflow.com/questions/10337944/sql-unique-constraint-across-multiple-tables – Jonas Metzler Apr 30 '22 at 14:25
  • Another possible solution is to create a unique index on ID in whatever table contains both the ID and Participant_Activity. That will constrain you to only one activity per ID. – Chris Maurer Apr 30 '22 at 15:58

1 Answers1

0

This one is trickier to do in SQL. If this were Rails, I'd say to use single table inheritance.

The entities are participant, swimmer, and runner tables, correct? And each participant can do only one activity.

One possibility:

  1. To participant, add type field (limited to swimmer and runner either as an enum, or with a reference table).
  2. To the swimmer and runner tables, add a constraint each that the participant_ID must have the corresponding type.
  3. To participant, add a before update trigger that deletes the child swimmer or runner record if the type changes.
SJTalbutt
  • 31
  • 2