'Oracle SQL create unique INDEX constraint based on status column and other 4 column

I need UNIQUE INDEX CONSTRAINT for below example:

 CREATE TABLE DEMO(
    
    COL_1 number,
    COL_2 number,
    COL_3 number,
    COL_4 number,
    STATUS number)
    ;
    
    Insert into DEMO(COL_1,COL_2,COL_3,COL_4,STATUS) values (1,2,3,4,0); --Allow insert
    Insert into DEMO(COL_1,COL_2,COL_3,COL_4,STATUS) values (1,2,3,4,1); --Allow insert
    Insert into DEMO(COL_1,COL_2,COL_3,COL_4,STATUS) values (1,2,3,4,0); --Allow insert
    Insert into DEMO(COL_1,COL_2,COL_3,COL_4,STATUS) values (1,2,3,4,1); --Not allow insert status 1 already exits!
sqloracleconstraints


Solution 1:[1]

You simply need a conditional index on the STATUS column. You can try below query -

CREATE UNIQUE INDEX UNQ_IDX_STATUS ON DEMO (CASE WHEN STATUS = 1 THEN STATUS ELSE NULL END);

This will only apply the constraint where the STATUS column will have the value as 1 else will have no impact on your other values in this column.

Solution 2:[2]

you could try this also, by creating a unique index which is a concenate of the columns.

CREATE   TABLE DEMO(
    COL_1 number(1),
    COL_2 number(1),
    COL_3 number(1),
    COL_4 number(1),
    STATUS number(1))
;

CREATE UNIQUE INDEX UQ_INDEX
ON DEMO(
(     CASE STATUS WHEN  1        THEN COL_1 || COL_2 ||COL_3 ||COL_4 ||STATUS 
     ELSE
         NULL
 END));

Insert into DEMO(COL_1,COL_2,COL_3,COL_4,STATUS) values (1,2,3,4,0); 
Insert into DEMO(COL_1,COL_2,COL_3,COL_4,STATUS) values (1,2,3,4,1);
Insert into DEMO(COL_1,COL_2,COL_3,COL_4,STATUS) values (1,2,3,4,0);
Insert into DEMO(COL_1,COL_2,COL_3,COL_4,STATUS) values (1,2,3,4,1);

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1 Ankit Bajpai
Solution 2 Himanshu Kandpal

Related Questions