CODEWITHSUNDEEP
sqloracle-databaseunique-constraint
ugc10586
ugc10586

Reputation: 13

How can I make a unique constraint order independently on two columns

I'm working with an Oracle Database and I need to create a table like below.

MAP(Point_One, Poin_Two, Connection_weight).

The table represents data about a graph. I would like to create a table with a constraint that prevents the insertion of an already existing connection.

For example, the table already contains this connection:

Point_One | Point_Two | Connection_weight
-----------------------------------------
p_no1     | p_no2     | 10

And the constraint would prevent the repeated insertion of this connection, even if I try to add the points in different order. (For example: (p_no2, p_no1, 10) )

A simple UNIQUE (Point_One, Point_Two) constraint is unfortunatelly not enough. Do you have any advice?

Upvotes: 1

Views: 298

Answers (2)

Martina
Martina

Reputation: 929

You can achieve the desired result easily by using trigger.

 create table map (point_one number, point_two number, connection_weight number)
/
 create or replace trigger tr_map before insert on map
 for each row
 declare
 c number;
   begin
     select count(1) into c from map where (point_one=:new.point_one and   point_two=:new.point_two)
     or
     (point_one=:new.point_two and point_two=:new.point_one);
     if c>0 then
     raise_application_error(-20000,'Connection line already exists');
  end if;
end;
  /

SQL> insert into map values (1,2,10);

1 row created.

SQL> insert into map values (2,1,10); insert into map values (2,1,10) * ERROR at line 1: ORA-21000: error number argument to raise_application_error of -100 is out of range ORA-06512: at "C##MINA.TR_MAP", line 10 ORA-04088: error during execution of trigger 'C##MINA.TR_MAP'

I'm still thinking about the CHECK constraint, but yet I didn't come with decission whether it is possible or not.

Upvotes: 0

Justin Cave
Justin Cave

Reputation: 231661

You can create a function-based index

CREATE UNIQUE INDEX idx_unique_edge
    ON map( greatest( point_one, point_two ),
            least( point_one, point_two ) );

I'm assuming that the data type of point_one and point_two is compatible with the Oracle greatest and least functions. If not, you'd need a function of your own that picks the "greatest" and "least" point for your complex data type.

Upvotes: 6

Related Questions