Geek asks and answers

Unique sql restriction on a combination of 2 columns

How to create a unique constraint for a combination of two values ​​in two columns.

Value

column1  column2 
   2        1

looking for a restriction prohibiting

column1  column2 
   1        2
+3
source share
3 answers

If your database allows expressions in the index, you can do something like this (ANSI SQL):

CREATE UNIQUE INDEX on your_table (least(column1, column2)
                              , greatest(column1, column2));

Please note that this is a unique index , not a unique restriction. The only difference for most DBMSs is that you cannot have a unique index as the target of the foreign key, but otherwise they fulfill the same goal.

If your DBMS does not have least()or greatest(), you can replace it with the CASE expression:

create unique index on your_table 
  (case column1 < column2 then column1 else column2 end, 
   case column2 > column1 then column2 else column1 end));
+5

2 2 ( ):

, TRIGGER, (ORACLE):

 CREATE TRIGGER trig1
        BEFORE INSERT ON TAB
        REFERENCING NEW AS NEW
        FOR EACH ROW
    DECLARE
        FOUND NUMBER;
    BEGIN
        SELECT COUNT(1) into FOUND FROM TAB WHERE
        (COLUMN1=:NEW.column2 AND COLUMN2=:NEW.column1) 
         OR (COLUMN1=:NEW.column1 AND COLUMN2=:NEW.column2); 
    IF FOUND>0 THEN
    raise_application_error (-20001,'INSERT not allowed');
    END IF;
        END trig1;

: .

0

Looking at the documentation, I found this for the ORACLE SGBD:

CREATE TABLE b(
 b1 INT, 
 b2 INT, 
 CONSTRAINT bu1 UNIQUE (b1, b2) 
                USING INDEX (create unique index bi on b(b1, b2)),
 CONSTRAINT bu2 UNIQUE (b2, b1) USING INDEX bi);

See the chapter “Specifying a Constraint-Related Index” on the ORACLE Documentation page .

Do this help.

0
source

More articles:


All Articles