Are Primary Keys Necessary?

[Sasquatch]

I read somewhere that *ALL* tables should have a primary key, but no
explanation was given as to why or what benefit it serves. After looking
around for an answer, I've decided to ask for myself...

Most of my tables already have primary keys but there are some tables with
only a few fields and no primary key.

For instance, I have a table called [Company_Table] with CompanyID as the
primary key. I have another table called [Company_Memo_Table] with 3 fields:
CompanyID (Indexed, Duplicates OK), MemoDate and MemoDescription, but no
primary key. The CompanyID fields relate in a one-to-many relationship....a
single company can have mutiple memos.

Since the CompanyID (foreign key) in the [Company_Memo_Table] is already
indexed, what benefit would be gained by adding a generic AutoNumber field as
the primary key if it will never be used in a relationship or query?

Thanks in advance.

 

[Rick Brandt]

I read somewhere that *ALL* tables should have a primary key, but no
explanation was given as to why or what benefit it serves. After
looking around for an answer, I've decided to ask for myself...

Most of my tables already have primary keys but there are some tables
with only a few fields and no primary key.

For instance, I have a table called [Company_Table] with CompanyID as
the primary key. I have another table called [Company_Memo_Table]
with 3 fields: CompanyID (Indexed, Duplicates OK), MemoDate and
MemoDescription, but no primary key. The CompanyID fields relate in
a one-to-many relationship....a single company can have mutiple memos.

Since the CompanyID (foreign key) in the [Company_Memo_Table] is
already indexed, what benefit would be gained by adding a generic
AutoNumber field as the primary key if it will never be used in a
relationship or query?

Thanks in advance.

PKs primary purpose is to unquely identify ONE row in your table. If you have a
field or fields that already do that then a PK is not technically "required".

However; to keep your database performing at its best you would want an index on
the field or fields that you use to identify a row and to keep the table's data
"clean" you would want that index to not allow duplicate values. If you do all
of that you have essentially created a PK without specifically calling it a PK.
The only difference between a PK and a unique index is that a unique index can
still allow some of the fields in the index to be Null. With a PK no fields
included are ever allowed to be Null.

Beyond that the designation of a PK is a form of documentation about your table
that a unique index is not quite as explcit about. On many systems the PK
designation might even control how the table is written to disk (the order), but
that is a physical issue that particular database creators choose to implement.
It is not really part of the definition of a PK. It is still good to know if
your database of choice chooses to do that though.

Now...it is important to make a distinction between "every table should have a
PK" and "every table should have an AutoNumber field" or "every PK should be an
AutoNumber". You can substitute any sort of arbitrary numbering system for
"AutoNumber" in those statements.

So called "surrogate keys" are often useful, and many developerss do use them
100% of the time, but that is a personal preference. It is not mandated by
relational theory nor even considered a "best practice" by any sort of majority
of the database builders of the world (many would argue the exact opposite).

So in your specific case, if you have already uniqely indexed your CompanyID
field then adding a numeric field and making it the PK would gain you nothing.

 

[Amy Blankenship]

I read somewhere that *ALL* tables should have a primary key, but no
explanation was given as to why or what benefit it serves. After looking
around for an answer, I've decided to ask for myself...

Most of my tables already have primary keys but there are some tables with
only a few fields and no primary key.

For instance, I have a table called [Company_Table] with CompanyID as the
primary key. I have another table called [Company_Memo_Table] with 3
fields:
CompanyID (Indexed, Duplicates OK), MemoDate and MemoDescription, but no
primary key. The CompanyID fields relate in a one-to-many
relationship....a
single company can have mutiple memos.

Since the CompanyID (foreign key) in the [Company_Memo_Table] is already
indexed, what benefit would be gained by adding a generic AutoNumber field
as
the primary key if it will never be used in a relationship or query?

If you were not using Access as the interface to your database and you
wanted to edit the description on only one record that had the same Company
ID and date, you might find it difficult. You'd have to specify al three
fields in the WHERE clause of the Update Query, and if the description were
also identical, you would actually be completely unable to change the
description of only one of the two records.

All of the theoretical stuff is cool, but a pragmatic demonstration of what
happens without a PK is sometimes more informative ;-).

-Amy

 

[Tim Ferguson]

I read somewhere that *ALL* tables should have a primary key, but no
explanation was given as to why or what benefit it serves. After
looking around for an answer, I've decided to ask for myself...

The simple answer is that, in R theory, if there is no key then there is
no table. The Relations in Relational theory have nothing to do with the
lines you draw on the relationships window: a Relation is the same as a
table.

It all comes from simple maths: the definition of a function is that for
every (valid) value of one thing, there is one value of something else:

Sin(1.2) = 0.93203908596722634967

And in the same vein:

Colour(Derek's car) = Red

but that only makes sense as long as there is one value of [Derek's car].
If Derek owns more than one car, then there is no sensible answer as to
what "its" colour is. In other words, the relation has to have a unique
key, [Which Car], in order to define a single value of Colour.

For instance, I have a table called [Company_Table] with CompanyID as
the primary key. I have another table called [Company_Memo_Table]
with 3 fields: CompanyID (Indexed, Duplicates OK), MemoDate and
MemoDescription, but no primary key. The CompanyID fields relate in a
one-to-many relationship....a single company can have mutiple memos.

So how do you know which memo to delete or edit..? If there is no key,
there is no way to tell apart all the memos from a particular CompanyID.


Hope that helps


Tim F

 

[Pat Hartman \(MVP\)]

To add to the other comments, if your tables are linked, Access will not
allow updates to any query you create that joins to a table without a
primary key or unique index.

 

[David W. Fenton]

So how do you know which memo to delete or edit..? If there is no
key, there is no way to tell apart all the memos from a particular
CompanyID.

If there's only one memo per CompanyID, then the CompanyID is the
PK, of course.