Conditional unique constraint in oracle db

Question

I have a situation where I need to enforce a unique constraint on a column[attribute] depending on another column value.

So for example, I have a table like Table(ID, EID, Name, ISDeleted)

ISDeleted can only have a value null or 'y' (active or deleted), and i want to create a unique constraint on EID, ISDeleted only when ISDeleted = null, since I dont care if there are multiple deleted records with the same id. Please note that, EID can have null value.

I am using Oracle DB for this.

Answer 1

You can't create a constraint. But you can create a unique function-based index. This takes advantage of the fact that Oracle does not index NULL values-- any rows where isDeleted is NOT NULL will not be included in the index so the unique constraint won't apply to them.

CREATE UNIQUE INDEX one_not_deleted
    ON table_name( (CASE WHEN isDeleted IS NULL
                         THEN eid
                         ELSE null
                      END) );