Hi TOM,
I try to create an unique constraint using FBI, it fails with "ORA-00904: : invalid identifier" error. It looks like "alter table" doesn't accept functions in expressions for unique constraints -
create table ctest (a integer, b integer);
create unique index ctest_idx on ctest (a, case when b=1 then 0 else 1 end);
alter table ctest add constraint ctest_b unique (b); -- fine
alter table ctest add constraint ctest_b2 unique (case when b=1 then 0 else 1 end) using index ctest_idx; -- ORA-00904: : invalid identifier
alter table ctest add constraint ctest_b1 unique (nvl(b,-1));
drop table ctest; -- ORA-00904: : invalid identifier
I know that unique index by itself provide desired uniqueness but usually I always accompany unique indexes with associated constraints.
In any case, is the inability to support functions in constraint expression a bug or has some reasons?
It is just a limitation of function based indexes.
However, you can create a virtual column with that expression, and that put a unique constraint on that column.
If you don't want to have the column, in 12c, you could also make it invisible.
SQL> create table t ( x int, y int generated always as ( x + 10 ));
Table created.
SQL> alter table t add constraint t_uq unique ( y );
Table altered.
SQL> alter table t modify y invisible;
Table altered.
SQL> insert into t values (1);
1 row created.
SQL> insert into t values (1);
insert into t values (1)
*
ERROR at line 1:
ORA-00001: unique constraint (MCDONAC.T_UQ) violated