Primary Keys and Foreign Keys

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.

ORDER
  ORDER_ID
  ORDER_DATA
...

ORDER_ITEMS
  ORDER_ID
  ITEM_ID
  OTHER ITEM DATA
...

add a third table ITMES that has all possible items ITEMS
  ITEM_ID
  ITEM_DATA
...

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

Thanks

Chris Received on Wed Nov 29 2000 - 16:40:54 CET