Oracle: function based index selective uniqueness

user_511280 0
Viewed 11972

I have to maintain history and so I am using is_deleted column which can have 'Y' or 'N'. But for any instance of is_deleted 'N' I should have uniwue entry for (a,b,c) composite columns.

When I am tryin to create function based unique index I am getting error.

CREATE UNIQUE INDEX fn_unique_idx ON table1  (CASE WHEN is_deleted='N' then (id, name, type) end);

ERROR at line 1: ORA-00907: missing right parenthesis

Please help.

Thanks

indexing oracle sql
2 Answers
CREATE UNIQUE INDEX fn_unique_idx ON table1  
    (CASE WHEN is_deleted='N' then (id||','|| name||','|| type) end);
user_39594120
Related