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?

Neither.

>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.


Regards,

Ann
www.ibphoenix.com
We have answers.