Is there in SQL a way to enforce unicity of undirected edge?

Question

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),
name varchar(100) unique,
unique(id_from, id_to)
);

With this schema, you could create 2 different journeys for a pair of locations, one for the way in and one for the way back. What I want is to enforce a single journey for each pair of locations. What are the options to do that ?

score 2 · Accepted Answer · answered Jan 03 '21 at 16:22

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

create table Journey (
    id integer primary key,
    id_from integer foreign key references Location(id),
    id_to integer foreign 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.

The _other_ method is to store an edge for each direction, which is useful if you need to calculate distance or closures, eg with https://learn.microsoft.com/en-us/sql/relational-databases/graphs/sql-graph-overview?view=sql-server-ver15 — David Browne - Microsoft, Jan 03 '21 at 16:31

score 1 · Answer 2 · edited Jan 05 '21 at 22:23

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

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 identity(1,1),
  id_from integer foreign key references Location(id),
  id_to integer foreign 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

Be careful of overflow with this. e.g. the from,to pair of 46341,46342 — Martin Smith, Jan 05 '21 at 22:26

Is there in SQL a way to enforce unicity of undirected edge?

2 Answers2

Linked

Related