Is There In Sql A Way To Enforce Unicity Of Undirected Edge?

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 int

Solution 1:

The simplest method is to enforce the "direction" and then use a unique constraint:

createtable Journey (
    id integerprimary key,
    id_from integerforeign key references Location(id),
    id_to integerforeign key references Location(id),
    name varchar(100) unique,
    unique(id_from, id_to),
    check (id_from < id_to)
);

However you have to remember to insert the values in order to use a trigger to ensure that they are in order.

Otherwise, you can use computed columns for the smallest and biggest values and then use a unique constraint on that.

Solution 2:

You can enforce unicity of undirected edge using sum and product computed columns:

createtable Location (
  id integerprimary key(1, 1),
  latitude decimal(8,6),
  longitude decimal(9,6),
  address varchar(100),
  name varchar(60) unique
);
createtable Journey (
  id integerprimary key identity(1,1),
  id_from integerforeign key references Location(id),
  id_to integerforeign key references Location(id),
  s as id_from + id_to persisted,
  p as id_from * id_to persisted,
  unique(s, p),
  name varchar(100) unique,
);

Is a correct method to enforce a single journey (either way in or way back) for each pair of locations. A quadratic equation has maximum two solutions. It has at least id_from and id_to. So equation xx - sx + p=0 always has exactly 2 solutions which are id_from and id_to. You can see mathematical explanations there https://math.stackexchange.com/questions/171407/finding-two-numbers-given-their-sum-and-their-product