Menu
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.
359
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.
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.
To select distinct combinations from two columns, you can use CASE statement.
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.
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.
122
If you are willing to make this a rule, you might try a CHECK constraint:
CONSTRAINT force_collation CHECK (FROM < TO)
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
