Hi all.
(Oracle 9i2)

I have a name column that up to now had a unique index+constraint to
prevent the same names.
I wanted to change this, so that the names get compared
case-insensitive.

I can use a function base index to achive this:
CREATE UNIQUE INDEX MYTABLE_C1 ON MYTABLE
(LOWER(NAME));

However, prior to now I had the following:
CREATE UNIQUE INDEX MYTABLE_C1 ON MYTABLE
(NAME);
ALTER TABLE MYTABLE ADD (
CONSTRAINT MYTABLE_C1 UNIQUE(NAME)
USING INDEX);

But I cannot create a constraint on lower(..)
I have found quite a few examples on the net which explain to use a
function based index to enforce similar constraints.

So what problems can arise if I set only a fbi and not also the
explicit constraint?

thanks for any pointers.

best regards,
Martin

On Nov 10, 7:01 am, "Martin T." <bilbothebagginsb... (AT) freenet (DOT) de>
wrote:
Since in version 9.2 the FBI will provide the functionality you want I
do not see any problem other than because you do not have a constraint
defined other than you will not be able to see the "constraint" by
querying DBA_CONSTRAINTS.

HTH -- Mark D Powell --