Jamie, the topic "should AutoNumbers be exposed to users?" and "do AutoNumbers
make good primary keys?" are completely different!
Agreed.
I consider all these to be distinct topics:
1) keys in relational theory,
2) primary keys in relational theory (deprecated),
3) artificial keys in databases,
4) surrogate keys in databases,
5) PRIMARY KEY in the SQL language,
6) PRIMARY KEY in the Jet product,
7) Autonumber in the Jet product.
My point was nothing to do with 2, 4, 5 nor 6; rather, it was whether
Jet Autonumbers (7) are suitable for use as artificial keys (1 and 3)?
I say no.
I would expect
any competent developer to do all of the other proper stuff like enforcing
uniqueness on other data fields so as to prevent duplication of data.
The point (John Vinson's) I was responding to was about a scenario
where all the available attributes do not provide a unique key...
Everything you say about
"keys" is true of AutoNumbers whether the user sees them or not.
....so if in the abovementioned scenario one is using an AutoNumber as
an artificial key then one *must* expose it, otherwise there's no
point in having it. Further, I don't think any flavour of Autonumber
is suitable for the human eye. I've no argument against an Autonumber
which nobody sees (e.g. Codd's description of a surrogate key i.e. not
visible even to database developers/DBAs).
Rather then adhering to some strict dogma one should evaluate each
situation.
Are you saying that we shouldn't have "rules of thumb" because there
might - shock horror - be exceptions to the rule?
The following are all true...
Oh yeah...?
One should not use a byte if one needs values greater than 255.
What about using the negative range of byte for extra 'headroom'?
Anyhow, I'd always use INTEGER (Long) in place of byte because it is
more portable.
One should not use an integer if one needs fractional values.
Some people use an integer type to scale their own decimal (e.g.
storing integer seconds rather than decimal hours). Me, I'd use
DECIMAL which, again, ports well. Some people say one should use
CURRENCY for up to four decimal places otherwise a floating point type
(usually FLOAT) because DECIMAL is somehow evil whereas I prefer the
fixed point DECIMAL wherever possible (as does Jet natively <g>) and
other people say other things but then that's the nature of
discussion...
One should not use an AutoNumber if they don't want gaps.
Isn't that a bit like saying, "One should not use an AutoNumber if
they don't want duplicate values" because there is nothing inherent in
Jet's AutoNumber functionality that guarantees uniqueness? If the
following can make an AutoNumber suitable if they don't want
duplicates...
CREATE TABLE Test (
ID IDENTITY(1, 1) NOT NULL PRIMARY KEY,
data_col INTEGER NOT NULL
);
....then it would follow that something like this would make AutoNumber
suitable if they don't want gaps...
CREATE TABLE Test (
ID IDENTITY(1, 1) NOT NULL PRIMARY KEY,
data_col INTEGER NOT NULL,
CONSTRAINT test__ID__no_gaps
CHECK (NOT EXISTS (
SELECT *
FROM Sequence AS S1
WHERE S1.Seq BETWEEN (SELECT MIN(T2.ID) FROM Test AS T2)
AND (SELECT MAX(T2.ID) FROM Test AS T2)
AND NOT EXISTS (
SELECT *
FROM Test AS T1
WHERE S1.Seq = T1.ID
)))
);
....where Sequence is a table of integers (seq), being a 'standard
issue' auxiliary table.
Perhaps a better assertion would have been, "One should not use an
incremental AutoNumber if they want to be able to UPDATE its values."
Jamie.
--
