Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Oracle SQL unique constraint A to B, B to A

Tags:

sql

oracle

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.

like image 359
4spir Avatar asked Mar 31 '13 15:03

4spir


People also ask

Can unique key Be combination of columns?

A composite unique key is a unique key made up of a combination of columns. Oracle creates an index on the columns of a unique key, so a composite unique key can contain a maximum of 16 columns.

How do you add unique constraints to multiple columns in SQL?

To define a UNIQUE constraint, you use the UNIQUE keyword followed by one or more columns. You can define a UNIQUE constraint at the column or the table level. Only at the table level, you can define a UNIQUE constraint across multiple columns.

How do I find unique two column combinations in SQL?

To select distinct combinations from two columns, you can use CASE statement.

How do you create a unique constraint in Oracle?

The syntax for creating a unique constraint using an ALTER TABLE statement in Oracle is: ALTER TABLE table_name ADD CONSTRAINT constraint_name UNIQUE (column1, column2, ... column_n); table_name.


2 Answers

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.

like image 122
a_horse_with_no_name Avatar answered Nov 10 '22 01:11

a_horse_with_no_name


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

CONSTRAINT force_collation CHECK (FROM < TO)
like image 27
BellevueBob Avatar answered Nov 10 '22 02:11

BellevueBob