Re: Surrogate Keys As Part Of Composite Keys?

From: Thomas Muller <ttm_at_nextra.com>
Date: 2000/03/27
Message-ID: <37ID4.24828$6b1.450335_at_news1.online.no>#1/1


Randy Yates <yates_at_207.87.184.178> wrote in message news:38DE9DE1.9F43B4_at_207.87.184.178... [snip]
> Let me address Thomas's question of when you would use a separate
> primary key to B. First, it is "nice" to do this when using
> Access since you can make the primary key of B an "autonumber"
> field, so that when you insert a new record programmatically
> Access will automatically come up with new key value and therefore
> the program does not have to.

You will find this kind of feature in any RDBMS. This feature's existence should not, however, in any way affect the model.

> This would not be the case for a
> non-autonumber key in B. Second, if you did create a primary
> surrogate key in B, you can still select either i) a set of
> records corresponding to a specific order number (since order
> number will be a foreign key in B), or ii) a specific line
> order using the primary line order key.
>
> So, with the above implementation nicety as a reason *to* have
> a surrogate primary key, I ask again: what reason is there
> *to not* have a surrogate primary key but rather instead
> a composite key consisting of the surrogate (non-autonumber)
> key in B and the foreign key from table A?

First of all, "surrogate key" is not a common term within RDB terminology. I think you mean candidate key. A table may have several candidate keys (a key being a single/composite field(s) uniquely identifying a row), and you choose one of them as the primary key (PM).

I don't fully understand the question, but regarding the order <-> orderline relation, there's no need for a single field serving as candidate key, simply because this field wouldn't carry any useful information. The proper way to model the order/order_line relation is like this:

order(order_id, date, ....)
order_line(order_id, line#, article#, ...) // PM = (order_id, line#), line# ranges from 1...infinite for each order_id)

--

Thomas
Received on Mon Mar 27 2000 - 00:00:00 CEST

Original text of this message