Primary Key Question

[harperwork18]

Hello, I'm still trying to grapple with primary keys. I have many
tables that, through the help of these forums, are normalized. But many
of them do not have--at least I do not think they have--natural keys.
So I'm using autonumbers all over the place, which makes me queasy. I
know there are debates on using autonumbers and please, I'd like to not
see a repeat here; I'd just like it if an expert can look at a few
tables (below) and tell me if indeed it seems I can't help but use
autonumbers...thank you, harper

tblComputerTests
TestID autonumber PK
TestName (like "CompTest" and "TechSelfAssess"
UseBegan (date)
UseEnded (date) -- over time we've retired some tests and developed new
ones

tblComputerTestAreas (tests have sections that receive scores, then the
scores are tallied)
TestID FK
TestAreaID autonumber PK
TestAreaName(i.e. MSWord, MSPowerPoint--these names duplicate: the
tests have areas that are the same--for example, virtually all computer
assessments we've used test MSWord skills)

tblEmployeesandTestScores
EmployeeID PK
TestAreaID
Score

Questions: 1) do you see any way that the first two tables can get rid
of autonumbers? 2) what is the value of having 2-field primary keys and
would the first table be a candidate for that (TestID and TestName) 3)
Is it generally a bad idea to use text for a primary key?

Thank you!!

 

[jahoobob via AccessMonster.com]

The "thing" about autonumbers is that they should be significant only to the
database, not the user. A social security number or employee badge number
could be PKs and they have uses other than communication between tables and
are not autonumbered. Your use of autonumber PKs looks fine to me. They are
being used in creating the relationships in a relational database.

 

[Pat Hartman\(MVP\)]

I use natural keys if I have them and if I am certain that they are not
changeable. I only use multi-field primary keys if the table does not have
any children. This arises primarily with junction tables where the keys
from tblA and tblB become the multi-field primary key. However, if the
junction table will have a related many-side table, I add an autonumber and
use a unique index to enforce the business rule that the combination of
tblAkey and tblBkey occur only once.

 

[Steve Schapel]

Regarding Social Security Numbers as Primary Keys - this assumes:
- everybody has one
- they are unique
My understandinng is that in practice these assumptions are not true, so
SSN should never be used as a PK.

 

[Steve Schapel]

Harperwork,

It wouldn't make sense to use a composite primary key (involving more
than one field) if one of them was an Autonumber. TestID and TestName
would not be a candidate for this in your first table. Here's the
question I would ask... will the data in the TestName field *always* be
unique? Or is it possible that you could want two Test records with the
same name (maybe repeating the name of a previously "retired" test)? If
they will always be unique, I would delete the TestID field from this
table, make the TestName the primary key, and use the TestName in the
TestAreas table as the FK. In my opinion, the *only* reason for adding
an Autonumber field to the Tests table is if there will be repeated
values in the TestName. But then, as you noted, there are those who
will take an opposing viewpoint.

 

[onedaywhen]

It wouldn't make sense to use a composite primary key (involving more
than one field) if one of them was an Autonumber.

It makes perfect sense when you remember that PRIMARY KEY is used by
the implementation to determine clustering (physical order) on disk.

If I want to cluster on last_name that is not unique, I could append an
autonumber (ID) e.g. PRIMARY KEY (last_name, ID) in that order. Sure,
the autonumber has no meaning in the *logical model* but I don't care:
I'm only including it to satisfy the engine's requirement that the
clustered index (PRIMARY KEY) be unique. I can then use a UNIQUE
constraint for my natural key.

Jamie.

--

 

[onedaywhen]

many
of them do not have--at least I do not think they have--natural keys.

What about the compound of *all* the (non-autonumber) columns? If you
do not at *least* constrain these as UNIQUE (and you can probably do
better) then your table has no key at all i.e. the autonumber will not
prevent duplicates, rather it will 'trick' the database into thinking
there no duplicates when there are.

Remember that you are allowed to use a UNIQUE constraint for your
'primary key'. The PRIMARY KEY designation (SQL keywords in uppercase,
not shouting) has different meaning in each vendor product and in
Access/Jet it determines the (non-maintained) clustered index ordering
on disk. That's another debate to be had elsewhere... but my 'on topic'
point is that PRIMARY KEY does not have to be used for 'primary key'
(i.e. the key that will be primarily used the table because a table can
have multiple candidate keys) e.g. PRIMARY KEY *cannot* prevent
duplicates a valid-time state (history) table: you have to instead
constraint the 'primary key' using a CHECK constraint (truly
table-level validation rule).

Jamie.

--