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

Questions

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

February 2, 2022

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!

>Solution :

This is the 12th pseudo constraint (based on constraint types) that you can impose on a database model, that doesn’t require the use of the clause CONSTRAINT to define it.

You can create a partial unique index to enforce it. For example:

create unique index ix1 on demo (
  case when status = 1 then col_1 end,
  case when status = 1 then col_2 end, 
  case when status = 1 then col_3 end,
  case when status = 1 then col_4 end,
  case when status = 1 then status end
);

See running example at db<>fiddle.