Create a unique index when column=value-CodePudding

i have a table Decision : id, id_request, id_decision, comment, date. a request can be sent many times as long as it is not accepted, once it is accepted it cannot be sent again, so my question is how can create a unique index like this :

CREATE INDEX unique_decision 
ON Decision(id_request, id_decision)
where id_decision=1;

id_decision=1 means accepted

CodePudding user response：

SQL> create table Decision (
  2   id number,
  3   id_request number,
  4   id_decision number)
  5  ;

Table created


SQL> 
SQL> CREATE unique INDEX unique_decision
  2  ON Decision(id_request, case when id_decision = 1 then 0 else id end )
  3  ;

Index created

Insert id_request = 1 and null in id_decision (id_request =1)

SQL> insert into Decision (id, id_request, id_decision) values(1,1,null);

1 row inserted

Insert accepted decision as with id 3 (id_request =1).

SQL> insert into Decision (id, id_request, id_decision) values(3,1,1);

1 row inserted

try to update unaccepted decision with id 1

SQL> update Decision set id_decision = 1 where id = 1;
update Decision set id_decision = 1 where id = 1

ORA-00001: unique constraint (MY_WORK.UNIQUE_DECISION) violated