Re: Function bases index / conatrint - confused

On Nov 10, 7:01 am, "Martin T." <bilbothebagginsb..._at_freenet.de> wrote:
> 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

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 -- Received on Fri Nov 10 2006 - 08:45:04 CST