Subject Unique constraint not an index? was Re: Error:Object is in use
Author Helen Borrie
At 04:03 PM 23-02-01 +0100, you wrote:
>Helen Borrie wrote:
> > No. The unique constraint isn't an index, so it won't cause problems for the optimizer.
> >
> > >I still don't know if IB creates an index on unique, I think so, but I'm not
> > >sure.
> >
> > No, it doesn't...well, it kinda does...but it's an internal structure and it won't be used by the optimizer for joins.
> >
>Are You sure?
>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?

Try checking the output of this query:

select rdb$index_name, rdb$relation_name from rdb$indices
where rdb$relation_name = 'PARTS'

(I think this is similar to the query Jason uses to the get the data for the Indexes tab).

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?

>I'm using IB
>When the next Firebird version comes out, I think I will use Firebird

FB already has fixed a significant number of important bugs in the 0.9.4 betas. Not this one, though, since it hasn't been reported will be interesting to see what your query throws up.


All for Open and Open for All
InterBase Developer Initiative ยท