SQL 中是否有一种方法可以强制执行无向边的唯一性?

2021-09-14 00:00:00 sql sql-server unique-constraint undirected-graph 
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)
);

使用此架构，您可以为一对位置创建 2 条不同的旅程，一条用于进路，另一条用于返回.我想要的是为每对位置强制执行一次旅程.有哪些选项可以做到这一点?

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 ?

推荐答案

最简单的方法是强制执行方向"然后使用唯一约束:

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.

相关文章