0
create table Location (
  id integer primary key(1, 1),
  latitude decimal(8,6),
  longitude decimal(9,6),
  address varchar(100),
  name varchar(60) unique
);
create table Journey (
  id integer primary key(1,1),
  id_from integer foreign key references Location(id),
  id_to integer foreign key references Location(id),
  s = id_from + id_to persistent,
  p = id_from * id_to persistent,
  unique(s, p),
  name varchar(100) unique,
);

Is this a correct method to enforce a single journey (either way in or way back) for each pair of locations ?

Ludovic Aubert
  • 9,534
  • 4
  • 16
  • 28
  • 1
    Does this answer your question? [Is there in SQL a way to enforce unicity of undirected edge?](https://stackoverflow.com/questions/65552097/is-there-in-sql-a-way-to-enforce-unicity-of-undirected-edge) – SMor Jan 05 '21 at 21:56
  • The question is a complement to the question you are refering to – Ludovic Aubert Jan 05 '21 at 22:00
  • This is a potential answer to that question, not a new question. Post a self answer to that with this method – Martin Smith Jan 05 '21 at 22:02
  • A quadratic equation has maximum two solutions. It has at least id_from and id_to. So equation x*x - s*x + p=0 always has exactly 2 solutions which are id_from and id_to. – Ludovic Aubert Jan 05 '21 at 22:09
  • the question as to whether mathematically it is guaranteed that there is only one x,y pair where x + y = s and x*y = p is a maths question not a programming question. The question you link on maths se already shows this by expressing it as a quadratic equation. If you have additional questions about the maths element you should ask it there. Once that is confirmed it is just an answer to the duplicate question – Martin Smith Jan 05 '21 at 22:09
  • This approach does have the disadvantage that it constrains the values of from and to such that their product cannot be larger than max int though. A from of 46341 and to of 46342 will overflow – Martin Smith Jan 05 '21 at 22:14

1 Answers1

1

No. If you want unique edges regardless of direction, then use the smallest and biggest values:

create table Journey (
  id integer primary key(1,1),
  id_from integer foreign key references Location(id),
  id_to integer foreign key references Location(id),
  id_least as (case when id_from < id_to then id_from else id_to end) persistent,
  id_greatest as (case when id_from < id_to then id_to else id_from end) persistent,
  unique(least_id, greatest_id),
  name varchar(100) unique,
);
Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786