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:
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.
--