Re: Primary Keys

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

From: Rick Sawtell (r_sawtell_at_hotmail.com)
Date: 10/20/04 

Date: Wed, 20 Oct 2004 11:50:06 -0700

To avoid problems and other types of modifications to the data structures, I
would suggest switching to a surrogate key and making that the PK. You can
then create a UNIQUE index covering those two columns.

This surrogate key allows you more flexibility down the road. It's easier
to add tables, create the joins (less typing anyhow), modify the current PK
to something else etc.

HTH

Rick

"Rock" <rockisland@yahoo.com> wrote in message
news:OLbfeCttEHA.2804@TK2MSFTNGP14.phx.gbl...
> Sorry wasn't clear, you are correct, the tables have 2 columns combined to
> make a concatenated primary key. In some instances, one column is a
foreign
> key of another table.
>
> My next question: If a primary key consists of 2 columns, how would, or
> could you, make that a foreign key in another table, and how would you
query
> such a thing?
>
> Say for instance, I have a customer table, and an address table.
> The address table fields:
> CustomerID
> AddressDescription
> Address
>
> Sample Data:
> CustomerID AddressDescription Address
> 1 Main Office 123 Main
> Street
> 1 Secondary Location 456 Main Street
>
> So now I want to create another table that lists, for instance, people
> located at each office. How would I set up the foreign key to point to
the
> address table. Wouldn't it be necessary to create another field in the
> address table, maybe an identity field, and make that field the primary
key?
>
> And how would you query the database when joining tables? If you joined
the
> address table to a query, could you base it on both the CustomerID and
> AddressDescription being equal to another field in another table. You
can't
> do this, can you? So I guess efficiency really isn't my question, maybe
> more that it's a practicality or best practice issue: Under what
> circumstances would a 2-column primary key be preferable to a
nonmeaningful
> primary key, with perhaps constraints on the 2 columns if the combined
> uniqueness is the desired result?
>
> I hope I'm making sense, thanks for your thoughts! And thanks for the
> reminder about allowed nulls in the referenced columns, that could raise
> serious issues.
>
>
>
>
>
> "Anith Sen" <anith@bizdatasolutions.com> wrote in message
> news:OXm4hmstEHA.2184@TK2MSFTNGP12.phx.gbl...
> > >> I came across a database created by another person in the company
that
> > >> frequently has 2 primary keys on its tables. I'm not sure why it was
> > >> done this way, my guess is to create uniqueness with the 2-field
> > >> combination.
> >
> > Since 2 primary keys are impossible in a single SQL table, I guess you
> meant
> > it to be a single key with two columns.
> >
> > >> My question is: Is this poor design?
> >
> > The number of columns in a key by itself does not tell us whether a
> specific
> > design is good or bad. There is nothing wrong in having multi-column
keys;
> > however multi-column references can occasionally mess up data integrity
> > since SQL products allow NULLs in referencing columns.
> >
> > >> Shouldn't we create constraints or indexes if 2-field uniqueness is
the
> > >> purpose.
> >
> > Not necessarily. Keys by themselves are constraints and guarantee
> uniqueness
> > any way.
> >
> > >> Is one way more efficient than the other?
> >
> > That is mostly irrelevant since at the logical level, key selection
should
> > not be based on efficiency which is determined at the implementation
> level.
> > Considering the physical implementation level, unless we consider all
> > possible query combinations, data access paths and optimization rules
and
> > test out all of them on a specific system using on a specific dataset,
we
> > cannot generalize and conclude single column keys are efficient than
> > multicolumn keys or vice-versa.
> >
> > --
> > Anith
> >
> >
>
>

Relevant Pages

  • Re: Problem with Access concatenate query
    ... records in the final query. ... You probably need to INNER JOIN the tables, although on what column, I am not sure. ... PriceID -- Primary Key ... ItemID --- Foreign Key ...
    (microsoft.public.access.queries)
  • RE: Processing thousands of records
    ... Jerry Whittle, Microsoft Access MVP ... Access automatically creates an index for primary key fields. ... that the query is working faster, you don't need the 1stVisit02 query. ... where do I read about fundamental indexing and normalization? ...
    (microsoft.public.access.queries)
  • RE: Processing thousands of records
    ... Access automatically creates an index for primary key fields. ... that the query is working faster, you don't need the 1stVisit02 query. ... Jerry Whittle, Microsoft Access MVP ... where do I read about fundamental indexing and normalization? ...
    (microsoft.public.access.queries)
  • Re: Determining if a form has a table or query recordset source
    ... how to obtain the primary key of a query? ... the generic answer is: "who says any query has one?". ... > set for insertion into an audit trail table, ... > Dim audID As Long ...
    (microsoft.public.access.security)
  • Re: Why use a composite PK ever?
    ... Your ideas around surrogate key usage is completely wrong. ... we might actually need to use their DNA sequence. ... if you have used the primary key as the access through to the data from the ... completed set is presented to Foobar all at once, not a row at a time. ...
    (comp.databases)