sql server - 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 ?

Answer 1

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.