Using where clause in constraint or check constraint

Question

I have a requirement where unique constraint should be based on certain condition. If the status column is "LIVE" it should not allow another live record for the same algo_id. When I put unique constraint on this two column it does not allow to add multiple "OLD" status record.

TABLE: ALGO
ID : Number(10)  -- PK
Algo_id              VARCHAR2(30)      NOT NULL,
Status          VARCHAR2(30)     NOT NULL,

Algo_id  Status
ALGO-123 OLD
ALGO-123 OLD
ALGO-123 LIVE
ALGO-234 REMOVED
ALGO-234 REMOVED
ALGO-234 LIVE 
ALGO-234 LIVE <This should not allow as there is already live record for ALGO-234>

Thanks is advance.

I have tried below

ALTER TABLE ALGO
ADD CONSTRAINT unique_live_algo UNIQUE (algo_id, stats );

Is there way where I can put some condition like below:

ALTER TABLE ALGO
ADD CONSTRAINT unique_live_algo UNIQUE (algo_id, stats ) where/when Status = 'LIVE';

Answer 1

Some databases support WHERE in a constraint definition, but Oracle does not seem to support this. However, we can try using a unique function based index here:

CREATE UNIQUE INDEX idx1 ON ALGO (
    CASE WHEN Status = 'LIVE' THEN algo_id END,
    CASE WHEN Status = 'LIVE' THEN Status END
);

For those records not having a status of LIVE, both values in the tuple would just be NULL. Since Oracle allows multiple NULL values with a unique constraint, therefore duplicates would be allowed. Otherwise, a unique constraint would be applied to (algo_id, Status).

I give credit to this excellent DBA Stack Exchange question/answer which solves this problem.