![]() |
Oracle: function based index selective uniqueness - Printable Version +- 0Day Forums (https://0day.red) +-- Forum: Coding (https://0day.red/Forum-Coding) +--- Forum: Database (https://0day.red/Forum-Database) +---- Forum: Oracle (https://0day.red/Forum-Oracle) +---- Thread: Oracle: function based index selective uniqueness (/Thread-Oracle-function-based-index-selective-uniqueness) |
Oracle: function based index selective uniqueness - pleasable554258 - 07-31-2023 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 RE: Oracle: function based index selective uniqueness - equity896711 - 07-31-2023 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. RE: Oracle: function based index selective uniqueness - Prononmiraculous438 - 07-31-2023 CREATE UNIQUE INDEX fn_unique_idx ON table1 (CASE WHEN is_deleted='N' then (id||','|| name||','|| type) end); |