CODEWITHSUNDEEP
sqloracle-database
4spir
4spir

Reputation: 1184

Oracle SQL unique constraint A to B, B to A

my table is something like

FROM   TO     DISTANCE              
------ ---- ---------- 
A      B    100  
B      C    100
Z      A    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

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

EDIT: 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.

Upvotes: 6

Views: 406

Answers (2)

user330315
user330315

Reputation: 

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

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

Upvotes: 11

BellevueBob
BellevueBob

Reputation: 9618

If you are willing to make this a rule, you might try a CHECK constraint:

CONSTRAINT force_collation CHECK (FROM < TO)

Upvotes: 2

Related Questions