Subject Re: [ib-support] Unique constraint not an index? was Re: Error:Object is in use
Author Ann W. Harrison
All -

The UNIQUE constraint does create an index called RDB$UNIQUE<n>
unless the constraint is named. An named constraint creates an index
with a name that reflects the constraint name. Those are normal
(and desirable) indexes as far as the optimizer is concerned. That's
been the behavior since forever - from our first SQL implementation.

> >I created the table with a unique constraint and if I look with IB_WISQL
> >it shows me an unique index on ORDER_ID, ORDER_POS
> >and an index on ORDER_ID
>This could be an anomaly in IB_WISQL or a bug introduced by IB 6 or...Ann?


>It shouldn't show the unique constraint (and didn't used to) so if IB 6 is
>now storing unique constraints in rdb$indices, IMO that's a bug that has
>potential to inhibit optimization.
>Ann, can you comment?

It's not showing the constraint, it's showing the index created to support
the constraint. If that index is confusing the optimizer, then the optimizer
is much too easily confused. No surprise there.


We have answers.