Conditional unique constraint in oracle db
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) );