問題描述
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)
);
使用此架構(gòu)，您可以為一對位置創(chuàng)建 2 條不同的旅程，一條用于進路，另一條用于返回.我想要的是為每對位置強制執(zhí)行一次旅程.有哪些選項可以做到這一點?
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 ?
推薦答案
最簡單的方法是強制執(zhí)行方向"然后使用唯一約束:
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)
);
但是，您必須記住插入值以使用觸發(fā)器來確保它們是有序的.
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.
這篇關(guān)于SQL 中是否有一種方法可以強制執(zhí)行無向邊的唯一性?的文章就介紹到這了，希望我們推薦的答案對大家有所幫助，也希望大家多多支持html5模板網(wǎng)！