问题描述
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.
这篇关于SQL 中是否有一种方法可以强制执行无向边的唯一性?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持编程学习网！