0

Consider this fictional data model:

enter image description here

Dudes and Dudettes can have a match. I would only want a couple to match once.

This would look something similar to this as a table:

Dud-ette
id, name
1, Peter
2, Nadine
3, Hans
4, Elle
5, Zoe

Match
id1, id2, time
1, 5, 2022-09-01
2, 3, 2022-09-03
3, 2, 2022-09-03
4, 3, 2022-08-24

Now see what happened: in the match-table we have two times the same relation, just inverse: (2,3) and (3,2). I can't constrain this by adding a key or unique constraint, since it would consider it as non redundant. Redundant would be (2,3) and again (2,3). It seems that this happens when you're making a recursive design, meaning a relation from an entity to itself.

I want that (2,3) is recognised as redundant of (3,2) and therefore forbidden. Is there any design solution or sql keyword for this?

Boris Däppen
  • 123
  • 5

1 Answers1

2

Constrain Match to have id1<id2, eg

alter table Match add constraint ck_StoreEachRelationOnce check (Id1 < Id2)
David Browne - Microsoft
  • 49,000
  • 3
  • 53
  • 102