Oracle SQL唯一约束A到B，B到A [英] Oracle SQL unique constraint A to B, B to A

问题描述

我的桌子就像

FROM   TO     DISTANCE              
------ ---- ---------- 
A      B    100  
B      C    100
Z      A    120

我需要向表中添加一个不允许插入的约束 B A 100或C B 100或A Z 120

i need to add a constraint to the table that won't let insert B A 100 or C B 100 or A Z 120

我有一个PRIMARY KEY (from, to)和CONSTRAINT const_u UNIQUE (to, from)，但是它没有按预期工作.

i have a PRIMARY KEY (from, to) and CONSTRAINT const_u UNIQUE (to, from) but it doesn't work as expected.

我也无法强行按字母顺序排列正确顺序，因为我无权访问插入逻辑 编辑#2:可以添加B A 50吗? -不，你不能.从A到B或从B到A应该只有一个唯一的距离，但不能同时有两个唯一的距离.

I also cannot force alphabetically correct order on insert, as i don't have access to the insert logic EDIT#2: Can you add B A 50 ? -- no, you can't. There should be only one unique distance from A to B or B to A but not both at the same time.

推荐答案

create unique index on the_table (least(from,to), greatest(from,to));

如果表(A，B，100)中已经存在，将阻止添加(B，A，100).

Will prevent adding (B,A,100) if (A,B,100) is already in the table.

这篇关于Oracle SQL唯一约束A到B，B到A的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！