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

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!

CodePudding user response：

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.

CodePudding user response：

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.

CodePudding user response：

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);