Re: Surrogate Keys As Part Of Composite Keys?

Thomas Muller wrote:
>
> <markp7832_at_my-deja.com> wrote in message news:8bljqi$m1a$1_at_nnrp1.deja.com...
> > In article <mPoD4.24171$6b1.437249_at_news1.online.no>,
> > "Thomas Muller" <ttm_at_nextra.com> wrote:
> > > The general rule is that for 1-n relationships from A to B, B has a
 primary
> > > key excluding the reference to A. In a n-m relationship, a new table
> > > including only the composite key as added "between" the original
 tables.
> > >
> > > --
> > >
> > > Thomas
> > >
> > I am not sure what Thomas is trying to say, but if you have a parent to
> > child relationship from A to B, let us say like, order header to order
> > line items, it is normal to carry the Key of A, the order number, to B
> > so that order number and line number would be the unqiue key of B. The
> > is no need to create a seperate primary key to B because when will you
> > ever use it? You will be accessing B based either on all lines for the
> > order number in A or by a specific order number, line item number
> > combination.
> >
>
> Thanks, Mark. My reply was a little too hasty, I meant that if B had a
> unique key (like an employee) the primary key should not include a reference
> to other tables, such as e.g. department. The order/orderline is an
> excellent example of a parent/child relationship where the child has only
> one candidate key which includes the reference to the parent.
>
> --
>
> Thomas

Thank you, Mark and Thomas, for your replies. Thomas, let me also say that it is precisely the order/line items type of relationship I am referring to.

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

-- 
% Randy Yates                   % "Midnight, on the water... 
%% DIGITAL SOUND LABS           %  I saw...  the ocean's daughter." 
%%% Digital Audio Sig. Proc.    % 'Can't Get It Out Of My Head' 
%%%% <yates_at_ieee.org>           % *El Dorado*, Electric Light Orchestra
http://207.87.184.178/index.htm