Rule when primary key is needed?

  • Thread starter Thread starter Roland Bengtsson
  • Start date Start date
R

Roland Bengtsson

Is there a rule when primary key is needed in a table?
For example I have small tables with 5 - 20 posts. These are used with
Comboboxes in another table. I realize now that a key isn't needed in
the small tables.

/Roland
 
Roland

<<<> Is there a rule when primary key is needed in a table?>>>>

When you create a table that is part of a "relational" database.

Comboboxes in another table.>>>>
Click to expand...

If you are talking about lookup values for a main table then you should
define a primary key and consider enforcing RI cascade update only on the
relationships between the "lookup" tables and the "main" table. This will
ensure that your main table can only contain valid values constrained by the
records in the "lookup" table and that you cannot delete a value from the
"lookup" table that has been used by a record in the "main" table.

The first part (after defining what you want to record) of designing a
database is to normalise the data that you wish to record in it. An early
part of the normalisation process is to choose the primary key for the data
that is to be normalised.

I am not sure what online references are available you may wish to check the
Access 2002 Knowledge Base article 283878 dated 10 October 2003. (Referenced
on MSDN Library DVD July 2004)

If you have Access 2003 Developer Extensions (ADE 2003) there is a
whitepaper called Understanding Normalization dated November 2002, the file
name is NORMALZE.MHT. This has been written by Mike Hernandez of Microsoft
and author of the book "Database Design for Mere Mortals" which I not read
for obvious reasons (vbg) :-) However, I would expect to be a good starter
based on the document that I have read that came with the ADE 2003

These are very similar and fairly simple introductions the whitepaper is a
bit more detailed, the KB Article does not discuss the normal forms above
Third (not required in many designs but worth knowing for when it is
needed). There may be other sources many will be wrong to very wrong these
are a fairly good starter.

<<<<I realize now that a key isn't needed in the small tables.>>>>

No, the size of the table has little to do with it, it is whether this table
is part of a relational database or not. A "lookup" table for a "main" table
is part of a relational database.

If you are not creating a relational database and say just creating a set of
application tables or temporary tables just about anything goes. Although
one must recommend that you do define the primary key even then or at least
an Index that is unique and does not permit nulls, otherwise what is to stop
you creating duplicate records.

Please note that although Access does allow you to create a table without a
primary key the practice is frowned upon and breaks the rules of
normalisation which can only cause you trouble in the long run.

--
Slainte

Craig Alexander Morrison


news:[email protected]...
 
Thanks for your long answer, so to summarize it, I need primary keys
even in my case with small tables (that have relations to each other)?
 
Is there a rule when primary key is needed in a table?
Click to expand...

Always. If it doesn't have a primary key it's not a table.
For example I have small tables with 5 - 20 posts. These are used with
Comboboxes in another table. I realize now that a key isn't needed in
the small tables.
Click to expand...

In this instance I'll just use a table with one field - the value to
be used as the combo's choice; make this field the primary key as
well. You can easily get the impression that a Primary Key must be an
Autonumber, but that's not the case; any field except a Memo or OLE
object can be a PK. The only requirement is that it must be unique in
the table; it's helpful if the field is also stable and reasonably
short.
 
....well splitting hairs here. (g)
Always. If it doesn't have a primary key it's not a table.
Click to expand...

Strictly speaking if it does not have a primary key defined it cannot be
said to be a relation (or a table in a relational database).

It can be a table, just not a very good one with a book under one leg and
another held on with some twine. :-)
 
Not that long I can assure you. In fact I think it was way too brief and
would recommend that you do check the references mentioned.

However the answer is Yes (100%), if they have relationships and are part of
a relational database.

Note: Joe and John are Brothers, Joe and John are relations, Brother is the
relationship.
 
Back
Top