Helen Borrie wrote:

>
> At 01:59 PM 23-02-01 +0100, you wrote:
> >Helen Borrie wrote:
> >
> > > Not stupid, just time-consuming. :)) I'd better write a handbook....
> >
> >Is it possible to prepay for the book and get the pages which are done?
>
> I don't know yet. There will be some kind of mechanism for subscribing to the review panel (free) but I haven't worked out the details yet.

This should be great.
Do you use examples in your book?

>
> > > >They never see ORDER_ID. I use it so they can delete an order without
> > > >really deleting it, and they can use the deleted Order_No for a new order.
> > > >They are so afraid of the tax office. They say, the tax office wants
> > > >continuously numbers.
> > >
> > > That's cool, very satisfactory. :))
> >
> >The tax office ? :-))
>
> No - I mean it's very satisfactory that you made the Order_No independent of any keys. :))
>
> >that case, I would want the stability of a unique surrogate PK. I don't like structures where humans are capable of bending keys.
> >
> >
> >But it is possible that the user can change the ORDER_POS.
> >And therefore I will change the structure.
> >Yes, you convinced me. :-)
> >
> >But which model is better?
> >
> >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
> >);
> >CREATE UNIQUE INDEX UIDX_P_ORDER_ID_POS ON PARTS (ORDER_ID, ORDER_POS);
> >CREATE INDEX IDX_P_DRAFT_NO ON PARTS(DRAFT_NO);
> >
> >Or
> >
> >
> >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 something else?
>
> Don't create an index for Parts(ORDER_ID) !!!! The foreign key constraint will cause this index to be created automatically. If you duplicate it, you will create ambiguity for the optimizer and it won't choose either index, but Natural order instead.

Yes, of course. I don't know what I thought.

But what happens if I create
CONSTRAINT FK_PARTS_ORDER_ID FOREIGN KEY (ORDER_ID) REFERENCES and
CONSTRAINT UK_ORDER_ID_POS UNIQUE (ORDER_ID, ORDER_POS)
or
CONSTRAINT FK_PARTS_ORDER_ID FOREIGN KEY (ORDER_ID) REFERENCES and
CREATE UNIQUE INDEX UIDX_P_ORDER_ID_POS ON PARTS (ORDER_ID, ORDER_POS);

IB will create 2 indexes. Is there any difference?

I have a form which shows the master table (order) and the appendant
parts.
Which index will be used?

>
> If you have no need for an index on (ORDER_ID, ORDER_POS), don't create an index.
> You said:
> > I have two tables (Work and ForeignFirm) who have a foreign key
> > (ORDER_ID, ORDER_POS) to Parts, with ON UPDATE CASCADE.
>
> Well, change that, so that the foreign key refers to PARTS_ID. It is the **row** that the relationship needs to be formed to, not the **meaningful data**. There will be no need for the on update cascade then.

Does it have a performance effect if I use
ON UPDATE NO ACTION
instead of
ON UPDATE CASCADE
on tablecreation, even if the update event never happens?

Regards,
Jörg Schiemann