Oracle: function based index selective uniqueness

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

You would need something like

CREATE UNIQUE INDEX fn_unique_idx
ON table1 (CASE WHEN is_deleted='N' THEN id ELSE null END,
CASE WHEN is_deleted='N' THEN name ELSE null END,
CASE WHEN is_deleted='N' THEN type ELSE null END);

An example of the constraint in action

SQL> create table table1 (
2 id number,
3 name varchar2(10),
4 type varchar2(10),
5 is_deleted varchar2(1)
6 );

Table created.

SQL> CREATE UNIQUE INDEX fn_unique_idx
2 ON table1 (CASE WHEN is_deleted='N' THEN id ELSE null END,
3 CASE WHEN is_deleted='N' THEN name ELSE null END,
4 CASE WHEN is_deleted='N' THEN type ELSE null END);

Index created.

SQL> insert into table1 values( 1, 'Foo', 'Bar', 'N' );

1 row created.

SQL> insert into table1 values( 1, 'Foo', 'Bar', 'Y' );

1 row created.

SQL> insert into table1 values( 1, 'Foo', 'Bar', 'Y' );

1 row created.

SQL> insert into table1 values( 1, 'Foo', 'Bar', 'N' );
insert into table1 values( 1, 'Foo', 'Bar', 'N' )
*
ERROR at line 1:
ORA-00001: unique constraint (SCOTT.FN_UNIQUE_IDX) violated


SQL> insert into table1 values( 1, 'Foo', 'Zee', 'N' );

1 row created.

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