Primary Key Question

[Matt]

Hi All,

I have inherited a database with very poor design. I am in the
process of redesigning the tables and had a dilema on what to use as
the primary key.

There are 4 columns that could make up a primary key and never have
issues.
I was thinking about creating a generic key (Autonumber) as a record
ID instead of the combination of the 4 fields.

 

[Guest]

Pros:

You only need one field in a child table as the foriegn key. If you have a
4-field PK, you need a 4-field FK.

If the 'meaning' of any of the data in the natural PK changes, you don't
have to worry about changing all the FKs. That said, if you have referential
integrity enableds and cascade update on, Access should do this for you.

Cons:

You still need a way to stop duplicates. Therefore the 4 fields need to be
in a unique index anyway.

Users often find it easier to track things down if you use a natural PK.

Me: I'd go with the autonumber PK in a heart beat.

BTW: I often find that the combination of multiple fields as a PK or unique
index often shows that the tables are properly normalized.

 

[Larry Daugherty]

Best and quickest would be that you google the Access newsgroups on
"Autonumber". Developers on both sides of that particular issue
passionately hold and defend their points of view... The debate has
raged since Access newsgroups came into being and that issue first
came up. Not much new information has surfaced over the years.

To me it has always come down to answering the question of which
paradigm will serve me best in terms of quality of results to the
client and best use of my time and least risk of new events causing
rework. Autonumber wins the nod in each of those issues.

However, along with my preference for the use of Autonumber surrogate
keys, I'm also the one who has shouted loudest and longest that
Autonumbers should be used for no other purpose than the creation of
surrogate keys and that they are unfit for human consumption. They
should *never* be seen by an end user. End users should never be
mucking about in the tables directly. I know that Microsoft proudly
flashes the ID field on every form created with their Autoform wizard.
MS is fallible, too! If you need the PK on the form, make it
invisible.

Enjoy your search and the reading.

HTH

 

[Pat Hartman \(MVP\)]

Stop! you're both right
A four-field PK becomes unwieldy when creating joins and impossible if you
need combos. I would go with the autonumber PK. But, it is important to
enforce the business rule of uniqueness for the four field "natural" key.
Do that by creating a unique index. That solves all your problems. The
business rules are enforced, the user gets to see fields he's comfortable
with, and you get to do your work with the easier to use autonumber behind
the scenes.

 

[Tom Ellison]

Dear Matt:

I suggest that, if you use an autonumber column, you not make it the primary
key. Make it a separate unique index. Use the 4 column natural key as the
primary key.

Why? There is no functional difference between a unique index and the
primary key, except this: When you Compact and Repair the database, the
rows in the table are placed in the physical order deterined by the Primary
Key.

Now, the values in the autonumber column are not likely to be of any value
in establishing a useful order for the rows in the table. The 4 column
natural key is not unlikely to be the sequence in which rows are displayed
on the screen and on reports. It is unlikely the autonumber based sequence
would give you any performance boost. When you access the data
sequentially, the natural key order is likely to be of substantial help in
performance.

Does this make sense?

Tom Ellison
Microsoft Access MVP