At the database level I am trying to understand the difference between having a PK of one table be both a PK and FK in another table vs just being a FK.
As an example, I have ORDER, ORDER_ITEM and ITEM entities. According to my Kroenke text, in the ORDER_ITEM table the PK is Order#, LineItem# (one line for each item sold in an order) and item# is simply a FK in the ORDER_ITEM table. I think the PK of the ORDER_ITEM table should be Order#, Item#.
My guess is it has to do with what if you delete an item# from the ITEM table. IF it is part of the PK for ORDER_ITEM than that would be bad, but isn't it also bad if Item# is simply a FK in the ORDER_ITEM table? (I guess you couldn't turn on referential integrity between Item and Order_item)
THANKS for your help!
Why not simply make the orderItem refer to both ORDER and ITEM table for corresponding record via PK / FK.
Okay, here's the simple explanation:
1. If you think about an order, it has header information at the top, then individual order lines. You can order any item on any line. You can order the same item on different lines. The line doesn't care, all it cares about is what order it belongs to. This is called an IDENTIFYING relationship. You have to know what order a line belongs to before you can uniquely identify it. Therefore, the primary key of the ORDERLINE table should be (OrderID[fk], OrderLineNumber). You need both columns to uniquely identify the order line.
2. Once you've decide to order a particular item, you place that item number on an order line. Once again, the order line doesn't care what the item is, it just cares that the item number is a valid one. Therefore, it will need to have a way to guarantee that the item actuall exists. It does this via a foreign key relationship to the ITEM table. This is called a NON-IDENTIFYING relationship. It does not go into the primary key of OrderLine because you don't need the ItemNumber to uniquely identify the OrderLine.
Hope that helps!
We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.