Primary Keys

[rkc]

Re: Primary Keys

David W. Fenton wrote:

polite person <snip@snippers. comwrote in
news:b1ecb2tnlv c972dalokdhrh6i 0665n3hhs@4ax.c om:
>
>

>>However, if two records are the same apart from the value of the
>>surrogate key this is surely an error (duplicate record), so you
>>need to keep an eye on this in some way or other.

>
>
Why is it that those who make this point against surrogate keys
always assume complete stupidity about the maintenance of unique
indexes on the part of those who use the surrogate keys?

Because it is a valid assumption much of the time. Just think about
how often you have seen someone who posts a table design in response
to a question mention creating an index to prevent duplicate records.
Do you think the majority of people who have to resort to having their
data schema fed to them in a newsgroup are likely to know about creating
a unique index on multiple fields?

[LurfysMa]

Re: Primary Keys

On Fri, 14 Jul 2006 01:07:54 GMT, rkc
<rkc@rochester. yabba.dabba.do. rr.bombwrote:

>David W. Fenton wrote:

>polite person <snip@snippers. comwrote in
>news:b1ecb2tnl vc972dalokdhrh6 i0665n3hhs@4ax. com:
>>
>>

>>>However, if two records are the same apart from the value of the
>>>surrogate key this is surely an error (duplicate record), so you
>>>need to keep an eye on this in some way or other.

>>
>>
>Why is it that those who make this point against surrogate keys
>always assume complete stupidity about the maintenance of unique
>indexes on the part of those who use the surrogate keys?

>
>Because it is a valid assumption much of the time. Just think about
>how often you have seen someone who posts a table design in response
>to a question mention creating an index to prevent duplicate records.
>Do you think the majority of people who have to resort to having their
>data schema fed to them in a newsgroup are likely to know about creating
>a unique index on multiple fields?

Hmmm... and just why are YOU here? Are you a feeder or a feedee?

--
Running MS Office 2000 Pro on Win2000

[rkc]

Re: Primary Keys

LurfysMa wrote:

Hmmm... and just why are YOU here? Are you a feeder or a feedee?

Entertainment.

[onedaywhen]

Re: Primary Keys


LurfysMa wrote:

Most of the reference books recommend autonum primary keys, but the
Access help says that any unique keys will work.
>
What are the tradeoffs?

That is a good question.

He's the position, as I see it, in brief.

Codd introduced the idea of a primary key. He later realised that all
keys are valid and that he was previously thinking non-relationally
when he assumed one key would need to be nominated as 'primary'.

RM theory has since moved on from the concept of primary keys. It was
too late for SQL, though: SQL vendors implemented primary keys,
assuming the PK would be given special meaning, and the concept of PKs
was retro-fitted to the SQL standards.

You can replace all your PRIMARY KEY constraints with NOT NULL UNIQUE
because they logically equivalent. This is what the Access help means
as referred to by the OP. However, in terms of physical SQL
implementation, PRIMARY KEY has been given special meaning. This is why
you are (correctly) still urged to designate a PRIMARY KEY for all your
tables.

What few people tell you is *how* to choose the PK.

What it comes down to is this: for Access/Jet, what does PRIMARY KEY
give you that NOT NULL UNIQUE does not? What is the special meaning for
the particular product, Access/Jet?

The answer, for Access/Jet the PK determines the (non-maintained)
clustered index, the physical ordering on disk.

So the next question is: what makes the best clustered index? The
answer to this is that a clustered index favours BETWEEN clauses and
GROUP BY clauses in SQL DML (queries, etc). In other words, your choice
of PK in SQL DDL (design) is driven by you SQL DML (queries). The
paradox here is that you can't write SQL DML before you've written your
SQL DDL, so you need to keep your PK's under review.

If you've understood the above you should come to the conclusion that a
sole autonumber column will never make a good PRIMARY KEY in
Access/Jet, because a random/incrementing integer/GUID does not make a
good clustered index. I'd suggest that anyone who uses their autonumber
column in a BETWEEN or GROUP BY construct has got something wrong in
design and/or queries. I'd further suggest that anyone who uses BETWEEN
or GROUP BY constructs which do not include columns that comprise their
PKs are likely to have made a poor choice of PK.

Jamie.

--

[Lyle Fairfield]

Re: Primary Keys

onedaywhen wrote:

The answer, for Access/Jet the PK determines the (non-maintained)
clustered index, the physical ordering on disk.

Can you verify this?

[Lyle Fairfield]

Re: Primary Keys

onedaywhen wrote:

What it comes down to is this: for Access/Jet, what does PRIMARY KEY
give you that NOT NULL UNIQUE does not? What is the special meaning for
the particular product, Access/Jet?
>
The answer, for Access/Jet the PK determines the (non-maintained)
clustered index, the physical ordering on disk.

From

http://msdn2.microsoft.com/en-us/library/wd9d69b1.aspx

THE CLUSTERED PROPERTY IS IGNORED FOR DATABASES THAT USE THE MICROSOFT
JET DATABASE ENGINE BECAUSE THE JET DATABASE ENGINE DOES NOT SUPPORT
CLUSTERED INDEXES.

[onedaywhen]

Re: Primary Keys


Lyle Fairfield wrote:

The answer, for Access/Jet the PK determines the (non-maintained)
clustered index, the physical ordering on disk.

>
From
>
http://msdn2.microsoft.com/en-us/library/wd9d69b1.aspx
>
THE CLUSTERED PROPERTY IS IGNORED FOR DATABASES THAT USE THE MICROSOFT
JET DATABASE ENGINE BECAUSE THE JET DATABASE ENGINE DOES NOT SUPPORT
CLUSTERED INDEXES.

No need to shout.

Try reading more widely:

New features in Jet Version 3.0:

http://support.microsoft.com/default.aspx?id=137039

Quote: "Compacting the database now results in the indices being stored

in a clustered-index format. While the clustered index isn't maintained

until the next compact, performance is still improved ... The new
clustered-key compact method is based on the primary key of the table.
New data entered will be in time order."

ACC2000: Defragment and Compact Database to Improve Performance

http://support.microsoft.com/default.aspx?scid=kb;en-us;209769

Quote: "A disk defragmenter will place all files, including the
database file into contiguous clusters on a hard disk ... If a primary
key exists in the table, compacting re-stores table records into their
Primary Key order. This provides the equivalent of Non-maintained
Clustered Indexes"

I think the phrase 'not supported' is used to convey the fact that in
Jet you cannot specify the clustered index independent of the PRIMARY
KEY as you can in, say, SQL Server. It may just mean that there is no
syntax for CLUSTERED INDEX.

Regardless of what 'not suuported' means, clustered indexes definitely
exist for Jet and PRIMARY KEY is the way to leverage them.

Jamie.

--

[Jamie Collins]

Re: Primary Keys


onedaywhen wrote:

No need to shout.

BTW I tend to put SQL keywrods in uppercase e.g. PRIMARY KEY. Sorry if
you thought I was shouting.

Jamie.

--

[David W. Fenton]

Re: Primary Keys

"Rick Brandt" <rickbrandt2@ho tmail.comwrote in
news:SYAtg.6515 6$Lm5.9780@news svr12.news.prod igy.com:

David W. Fenton wrote:

>polite person <snip@snippers. comwrote in
>news:b1ecb2tnl vc972dalokdhrh6 i0665n3hhs@4ax. com:
>>

However, if two records are the same apart from the value of
the surrogate key this is surely an error (duplicate record),
so you need to keep an eye on this in some way or other.

>>
>Why is it that those who make this point against surrogate keys
>always assume complete stupidity about the maintenance of unique
>indexes on the part of those who use the surrogate keys?

>
I think making such an assumption about anyone asking these kinds
of questions concerning primary keys is a very safe one to make.
The truth is that most Access users (not developers) don't have a
clue about basic database design and such warnings are very much
warranted.

Do you think the people participating in this recurring discussion
in this newsgroup at a theoretical level are really that stupid?

Put another way, do you honestly think *I* would use surrogate keys
without appropriate indexes on the natural keys? I wouldn't think
that anyone else in CDMA who is participating in the *theoretical*
discussion of surrogate vs. natural key would make that mistake, but
the natural-key advocates always think it's some kind of brilliant
riposte to the whole idea of surrogate keys.

--
David W. Fenton http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/

[David W. Fenton]

Re: Primary Keys

rkc <rkc@rochester. yabba.dabba.do. rr.bombwrote in
news:K7Ctg.2251 1$O35.7385@twis ter.nyroc.rr.co m:

David W. Fenton wrote:

>polite person <snip@snippers. comwrote in
>news:b1ecb2tnl vc972dalokdhrh6 i0665n3hhs@4ax. com:
>>

>>>However, if two records are the same apart from the value of the
>>>surrogate key this is surely an error (duplicate record), so you
>>>need to keep an eye on this in some way or other.

>>
>Why is it that those who make this point against surrogate keys
>always assume complete stupidity about the maintenance of unique
>indexes on the part of those who use the surrogate keys?

>
Because it is a valid assumption much of the time. Just think
about how often you have seen someone who posts a table design in
response to a question mention creating an index to prevent
duplicate records. Do you think the majority of people who have to
resort to having their data schema fed to them in a newsgroup are
likely to know about creating a unique index on multiple fields?

Who ever asks the question who is that dumb?

Honestly.

The question only comes up for someone who is smart enough to
understand the issues.

And the threads in this newsgroup on the topic have been mostly
theoretical discussions conducted by regular members of the group
who are obviously not newbies. Yet, every time, somebody feels it
necessary to repeat the obvious, something that has *nothing* to do
with the question of surrogate vs. natural keys, but is really only
a question of proper indexing.

I find the assumption of stupidity on the part of those using
surrogate keys to be quite offensive, and an intellectually
dishonest debating tactic.

YMMV.

--
David W. Fenton http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/

[David W. Fenton]

Re: Primary Keys

"onedaywhen " <jamiecollins@x smail.comwrote in
news:1152873446 .527450.271150@ h48g2000cwc.goo glegroups.com:

If you've understood the above you should come to the conclusion
that a sole autonumber column will never make a good PRIMARY KEY
in Access/Jet, because a random/incrementing integer/GUID does not
make a good clustered index. I'd suggest that anyone who uses
their autonumber column in a BETWEEN or GROUP BY construct has got
something wrong in design and/or queries. I'd further suggest that
anyone who uses BETWEEN or GROUP BY constructs which do not
include columns that comprise their PKs are likely to have made a
poor choice of PK.

A random PK would result in the placement of records on as many data
pages as possible, thus improving concurrency.

--
David W. Fenton http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/

[Jamie Collins]

Re: Primary Keys


David W. Fenton wrote:

If you've understood the above you should come to the conclusion
that a sole autonumber column will never make a good PRIMARY KEY
in Access/Jet, because a random/incrementing integer/GUID does not
make a good clustered index.

>
A random PK would result in the placement of records on as many data
pages as possible, thus improving concurrency.

Good point, I was thinking too narrowly.

If my table had columns for surname, initials and telephone number and
my queries predominantly use BETWEEN on the surname column, having the
table physically ordered on telephone number may make my queries
perform worse than if the physical order was on surname (can you
imagine trying to use a paper copy telephone directory ordered on
telephone number <g?!)

As I said, the choice of PK should be determined by the SQL DML e.g.
you are interested in page locks for updates in a multi-user
environment, I'm interested in query performance, etc.

Jamie.

--

[Rick Brandt]

Re: Primary Keys

David W. Fenton wrote:

"Rick Brandt" <rickbrandt2@ho tmail.comwrote in
news:SYAtg.6515 6$Lm5.9780@news svr12.news.prod igy.com:
>

David W. Fenton wrote:

polite person <snip@snippers. comwrote in
news:b1ecb2tnlv c972dalokdhrh6i 0665n3hhs@4ax.c om:
>
However, if two records are the same apart from the value of
the surrogate key this is surely an error (duplicate record),
so you need to keep an eye on this in some way or other.
>
Why is it that those who make this point against surrogate keys
always assume complete stupidity about the maintenance of unique
indexes on the part of those who use the surrogate keys?

I think making such an assumption about anyone asking these kinds
of questions concerning primary keys is a very safe one to make.
The truth is that most Access users (not developers) don't have a
clue about basic database design and such warnings are very much
warranted.

>
Do you think the people participating in this recurring discussion
in this newsgroup at a theoretical level are really that stupid?
>
Put another way, do you honestly think *I* would use surrogate keys
without appropriate indexes on the natural keys? I wouldn't think
that anyone else in CDMA who is participating in the *theoretical*
discussion of surrogate vs. natural key would make that mistake, but
the natural-key advocates always think it's some kind of brilliant
riposte to the whole idea of surrogate keys.

The OP was not one of these people (to my knowledge). Also, I don't feel that
newsnet discussions exist strictly for the benefit of those engaging in them.
Often points being made are for the benefit of other lurkers.

--
Rick Brandt, Microsoft Access MVP
Email (as appropriate) to...
RBrandt at Hunter dot com

[Jamie Collins]

Re: Primary Keys


Jamie Collins wrote:

If my table had columns for surname, initials and telephone number and
my queries predominantly use BETWEEN on the surname column, having the
table physically ordered on telephone number may make my queries
perform worse than if the physical order was on surname (can you
imagine trying to use a paper copy telephone directory ordered on
telephone number <g?!)
>
As I said, the choice of PK should be determined by the SQL DML e.g.
you are interested in page locks for updates in a multi-user
environment, I'm interested in query performance, etc.

Oops! I meant to add:

In other words I want to fetch rows on the same page and contiguous
pages; you want to maximise the chances of the rows each user will be
interested in are on different pages (am I correct?) I think in my
simple contacts example physically ordering on surname would provide
good concurrency as well. Whatever, it's clear we are both thinking
about the Jet implementation (i.e. contiguous storage on disk) when
considering PKs. Can everyone else say the same?

Jamie.

--

[Lyle Fairfield]

Re: Primary Keys

Regardless of what 'not suuported' means, clustered indexes definitely

exist for Jet and PRIMARY KEY is the way to leverage them.

One can get some of the advantages of clustered indexes by choosing a
meaningful primary key, by compacting ... and, perhaps, by defragging.
This is a far cry from the convenience and power or a clustered index.