Using where clause in constraint or check constraint [duplicate]

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

Side note: Prefer integers over strings in the case you have to cover only a specific set of values, they are less error prone (consider case mismatches, for instance), more performant to look up and require less storage and memory. — Aconcagua
– Aconcagua, Commented Nov 22, 2023 at 7:39

Tim Biegeleisen · Accepted Answer · 2023-11-22 07:50:26Z

4

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.

edited Nov 22, 2023 at 7:50

answered Nov 22, 2023 at 7:44

Tim Biegeleisen

526k32 gold badges324 silver badges399 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

astentx Over a year ago

status is redundant here, because it will contain constant value (LIVE) for this particular code.

Tim Biegeleisen Over a year ago

@astentx Sorry, I don't follow your comment. When the status column has LIVE as the value, we want to apply a unique constraint to the tuple (algo_id, Status). Otherwise, we don't want such a unique constraint.

MT0 Over a year ago

@astentx means you just need CREATE UNIQUE INDEX idx1 ON ALGO (CASE WHEN Status = 'LIVE' THEN algo_id END);

astentx Over a year ago

@TimBiegeleisen Yes, so CASE WHEN Status = 'LIVE' THEN Status END will not add more uniqueness, because it maps wider range into two values: null (which will not be indexed, because entire tuple becomes a tuple of nulls for this case) and LIVE, which corresponds to the same original value (1-to-1).

Collectives™ on Stack Overflow

Using where clause in constraint or check constraint [duplicate]

1 Answer 1

4 Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Linked

Related