Do all tables have to have a primary key?

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have a few people in my office that are building a new database. A few of
the tables they do not have a primary key set. These tables are very minor
and they will be used as look-up tables, but I thought every table should
have a primary key, no matter how minor the table. What are your thoughts...

I usually set an autonumber on the tables like this as a primary key. Am I
teaching good habits or just going overboard? Thanks, m.
 
Maura

Are you trying to start a religious war or something?! <g>

Can you create a table in Access that has no primary key? - Yes.

Does a "minor... look-up" table work without having a primary key? - Yes.
(but define "minor"...)

At least in theory, any row in any table needs a way to uniquely identify
that row (i.e., PK) (but to paraphrase something I saw the other day, ... in
theory, there should be no difference between theory and practice, but in
practice, theory and practice don't match up...)

JOPO (just one person's opinion)

Regards

Jeff Boyce
Microsoft Office/Access MVP
 
You're teaching good habits (although you'll get a very heated debate as to
whether you should always use autonumbers, or a natural key)
 
but I thought every table should
have a primary key, no matter how minor the table.

If it doesn't have a primary key, it's not a table.

In practical terms, what method is currently being used to identify which
row of a "minor" table is to be looked-up? Whatever field or combination of
fields is being used - that is the primary key, whether you have bothered
to tell the db engine about it or not.

HTH


Tim F
 
Does a "minor... look-up" table work without having a primary key? - Yes.

....but only until you get a duplicate in the table, then you start
wondering why you have twice the number of expected rows as a result
of your INNER JOIN. Make the sole column the key and the problem goes
away (plus you get instant 5NF said:
define "minor"

Static values that are few in number i.e. something that could have
been hard-coded into a validation rule in the first place ;-)
Are you trying to start a religious war or something?! <g>

I suggest dropping the word 'primary' and concentrating on 'keys'.

Every table should have a key that exists in the reality being
modelled (otherwise your design is wrong); on rare occasions you may
have to invent a key and expose it (note few people recommend exposing
an autonumber). Some tables will have more than one key (candidate
keys) and each should be enforced with a constraint. Some keys are
subtle (e.g. a sequenced constraint in a history table), requiring one
to look beyond Access/Jet's 'index' flavour of constraints (Jeff, you
may recall my Payroll example where multiple complex constraints are
required to ensure no duplicate data).

Once you have your keys in place, it is mere icing on the cake to make
the arbitrary decision as to which gets the PRIMARY designation.
Because PRIMARY KEY is implemented in Jet using an index, it follows
that only the 'index' flavour of constraints can be PRIMARY KEY. The
criteria on which to base the choice differs between SQL products; for
Access/Jet, the special features of PRIMARY KEY include:

1) Clustering (physical ordering) on disk after compact;
2) Appearing as bold text in the Relationships diagram;
3) In absence of explicit definition, it will be used when the table
is referenced in a relationship or FOREIGN KEY (the letter behaviour
is defined in the SQL-92 standard);
4) Prevents you from being nagged, "Although a primary key isn't
required..." (a completely spurious message anyhow!)

Jamie.

--
 
Back
Top