On Aug 17, 1:35 pm, "BruceM" wrote:
You have completely lost me when you say the PK
designation is always arbitrary. Are you saying the designation is
arbitrary?
Yes.
Consider my favourite example: a temporal database with a business
rule, "at no time can an employee have two salaries." The logical
model identifies a table EmployeeSalaryHistory comprising
employee_number, salary_amount, start_date and end_date; all columns
required; end_date = maximum date (#9999-12-31 23:59:59# in
implementation) is used to represents a row in the current state
current. The logical model candidate keys a
(employee_number, start_date)
(employee_number, end_date)
(employee_number, start_date, end_date)
In implementation we can put a SQL UNIQUE constraint on all of the
above but I choose to omit the third one because it's already covered
by the first two; this is an example how something in the logical
model may not directly related to a single corresponding object in the
implementation.
There's something missing of course: the above candidate keys only
ensure each *period* is unique, whereas the business rule is "at no
time". To cut a long (and hopefully familiar) story short, we need a
sequenced key where overlapping periods are prevented (in
implementation a CHECK constraint may be used plus some other
embellishments).
It is this sequenced key that I would consider as being the logical
primary key (lowercase) of the table.
As described we have implemented a table with a primary key but no SQL
(the language) PRIMARY KEY (PK) designation. Just about everyone says
that every SQL table should have a PK. Take a look in the ANSI SQL
spec:
1) "none of the values in the specified column or columns be the null
value" (section 4.10).
2) PK is a unique constraint (section 4.10.2).
3) there can only be one PK per table (section 11.7).
4) For the references specification of a FOREIGN KEY: "If the
referenced table and columns does not specify a reference column
list, then the table descriptor of the referenced table shall include
a unique constraint that specifies PRIMARY KEY" (section 11.8) [i.e.
you don't specify the columns involved, those of the PK will be used.]
That's it as far as SQL (the language) is concerned. The unwritten
implication is that PRIMARY KEY will have an 'implementation specific'
meaning. For Access/Jet, they a
1) Determines clustering (physical ordering) on disk on compact.
2) PK columns appear as bold text in the Relationships diagram.
3) If you don't specify a PK the Access interface will nag you,
"Although a primary key isn't required, it's highly recommended. A
table must have a primary key for you to define a relationship between
this table and other tables in the database [incorrect!]. Do you want
[Access] to create a primary key now?"
The choice is arbitrary. Whereas it is objectively demonstrable
whether a table is in, say, 3NF, you can't say look at a table and
say, "Hey, you've picked the wrong candidate for PK there," because
the choice of PK subjective, determined by personal preference,
prejudice and ignorance.
Back to the EmployeeSalaryHistory: we have two candidate keys defined
using UNIQUE. Do I really have to choose one to promote to PK. Just
about everyone says every table should have a PRIMAY KEY ...or do they
really mean primary key? FWIW David Portas SQL Server MVP seems to
have similar dilemmas:
Down with Primary Keys?
http://blogs.conchango.com/davidport...eys_3F00_.aspx
I say that yes, every table should have a PRIMARY KEY because
otherwise at the very least you are missing out on the
implementation's meaning; worse, though, you would be at the mercy of
the implementation's designers' default behaviour e.g. in Access/Jet
another NOT NULL (?) UNIQUE will be used for physical ordering
(clustering) but this is undocumented and because I have no idea how
the other candidates are eliminated (first one defined
chronologically? one defined on first columns in left-to-right order?
one defined on first columns in chronologically created order? does
nullable columns affect the choice) I'll make an explicit choice based
on my own criteria.
I've previously mentioned a 'telephone directory' style table where
phone number is the real world unique key and clustering would favour
last name and because in Access/Jet there is no explicit way of
specifying clustering then PRIMARY KEY (last_name, phone_number) would
be best for these aims. Thus, in this scenario the PRIMARY KEY isn't
even a candidate key!
when I refer
to a PK I mean the field or fields that are used for relationships.
Do you not have tables for which more than one key are referenced by
other tables?
Jamie.
--