Oracle: Functional Selective Uniqueness

Question

I need to maintain a story, so I use the is_deleted column, which can have a "Y" or a "N". But for any instance of is_deleted 'N' I have to have an uniwue entry for the (a, b, c) compound columns.

When I try to create a functional unique index, I get an error.

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

ERROR on line 1: ORA-00907: missing valid parenthesis

Please, help.

thanks

Answer 1

You will 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); 

Action restriction example

 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.