Helen Borrie wrote:

[SNIP]

> >If I create a parts table like
> >CREATE TABLE PARTS
> >(
> > PARTS_ID D_PARTS_ID
> > ORDER_ID D_ORDER_ID,
> > ORDER_POS D_ORDER_POS,
> > ORDER_POS_OLD D_ORDER_POS DEFAULT 0,
> > DRAFT_NO D_DRAFT_NO NOT NULL,
> > APPELLATION D_APPELLATION NOT NULL,
> > AMOUNT_ORDERED INTEGER NOT NULL,
> > .
> > .
> > .
> > DELETED D_BOOLEAN,
> > CONSTRAINT PK_PARTS_ID PRIMARY KEY (PARTS_ID),
> > CONSTRAINT FK_PARTS_ORDER_ID FOREIGN KEY (ORDER_ID)
> >REFERENCES ORDER (ORDER_ID)
> > ON DELETE NO ACTION
> > ON UPDATE CASCADE,
> > CONSTRAINT UK_ORDER_ID_POS UNIQUE (ORDER_ID, ORDER_POS)
> >);
> >CREATE INDEX IDX_P_DRAFT_NO ON PARTS(DRAFT_NO);
> >CREATE INDEX IDX_P_ORDER_ID ON PARTS(ORDER_ID);
> >
> >Or is the index IDX_P_ORDER_ID senseless because of UK_ORDER_ID_POS?
>
> 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

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

> Yes. In your PARTS example here, the composite key would be atomic if both columns had no significance other than to make the row unique. But if ORDER_POS can be changed for some reason, then it is NOT atomic.
>
> >Has a PK to be atomic?
>
> For integrity, yes. Will it work if it's not atomic? Yes.
>
> Should I use a parachute when I plan to jump out of an aircraft at 7000 M? Yes. Could I jump without a parachute? Yes.

<<g>> Ok, I got it.

Regards,
Jörg