Re: Primary Keys and Foreign Keys

From: Jan Hidders <>
Date: 29 Nov 2000 17:07:26 GMT
Message-ID: <903d4e$5db$>

Chris Collins wrote:
> This is releated to the thread titled 'primary key in a hierarchy'.
> Take for instance an ORDER has ORDER_ITEMS this can be represented in two
> tables very nicely.
> ...
> ...
> add a third table ITMES that has all possible items
> ...
> In my opinion the keys should be as follows:
> alter table ORDER add constraint primary key (ORDER_ID);
> alter table ORDER_ITMES add constraint primary key (ORDER_ID, ITEM_ID);
> alter table ITEMS add constraint primary key (ITEM_ID);
> alter table ORDER_ITEMS add constraint FOREIGN key "fk1" (ORDER_ID)
> references ORDER on (ORDER_ID);
> alter table ORDER_ITEMS add constraint FOREIGN key "fk2" (ITEM_ID)
> references ITEMS on (ITEM_ID);
> Note: ORDER_ID and ITEM_ID are external data not some internal counter.
> this is data that the user will see and use.
> Please offer fead back on problems with this or other methods of doing this
> and why they are better.

I see nothing wrong with your design; it is the standard way to represent a many-to-many relationship. I would consider optimization by introducing articial primary keys only if the applications hide the table design from the end-users. But even then you are probably either making things more complicated or are ignoring some database constraints.

> I am currently evaluating many ERD Datamodel designing software and
> none have allowed me to have on column participate in both a primary
> key and a foreign key.

Can you tell us which ones you are evaluating?

  Jan Hidders
Received on Wed Nov 29 2000 - 18:07:26 CET

Original text of this message