Geek asks and answers

Unique Oracle SQL constraint from A to B, B to A

my table is like

FROM TO DISTANCE              
------ ---- ---------- 
AB 100  
BC 100
Za 120

I need to add a constraint to the table that will not allow to insert B A 100or C B 100orA Z 120

I have PRIMARY KEY (from, to)and CONSTRAINT const_u UNIQUE (to, from), but it does not work as expected.

EDIT: I also cannot force the insertion order in alphabetical order since I do not have access to the insertion logic EDIT # 2: Can you add a BA 50? - No, you can not. There should be only one unique distance from A to B or B to A, but not at the same time.

+5
source share
2 answers
create unique index on the_table (least(from,to), greatest(from,to));

(B, A, 100) will not be added if (A, B, 100) is already in the table.

+10

, CHECK:

CONSTRAINT force_collation CHECK (FROM < TO)
+2

More articles:


All Articles