By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,446 Members | 3,031 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 435,446 IT Pros & Developers. It's quick & easy.

Primary Keys

P: n/a
Most of the reference books recommend autonum primary keys, but the
Access help says that any unique keys will work.

What are the tradeoffs?

I have several tables that have unique fields. Can I use them as
primary keys or should I define an autonum primary key?

One table has information about the 50 states in the US. The table
looks like this:

State Capitol Date Admitted
Alabama Montgomery December 14, 1819
Alaska Juneau January 3, 1959
Arizona Phoenix February 14, 1912

Since the state names are unique, is there any reason not to make that
field the primary key?

Another table has to do with grade school multiplication tables. This
is a simple table something like this:

Factors Answer
1x1 1
2x1 2
2x2 4
3x1 3
3x2 6
3x3 9
...
12x1 12
12x2 24
...
12x12 144

Here again, the Factors field (a text field) is unique. Is there any
reason not to use it as the primary key?

In both cases, the tables are small, so adding another 4-byte field is
no big deal, but I'd like to keep the tables as simple as possible
unless there is some downside.

Thanks

--
Running MS Office 2000 Pro on Win2000
Jul 12 '06 #1
Share this Question
Share on Google+
115 Replies


P: n/a
In my opinion, you're asking for trouble if you ever show the user the
primary key or if you might ever want to edit that information. Since
primary keys are normally the way you establish relationships, you don't
want them to ever change once a record has been created. Even though you
may think the key value won't change, typos have been known to happen.
Also, number fields take up less space in the database and primary keys, as
the source of the relationship, are typically repeated over and over in many
tables.

Therefore, I always use autonumbers. Other opinions vary.

HTH;

Amy

"LurfysMa" <in*****@invalid.invalidwrote in message
news:1f********************************@4ax.com...
Most of the reference books recommend autonum primary keys, but the
Access help says that any unique keys will work.

What are the tradeoffs?

I have several tables that have unique fields. Can I use them as
primary keys or should I define an autonum primary key?

One table has information about the 50 states in the US. The table
looks like this:

State Capitol Date Admitted
Alabama Montgomery December 14, 1819
Alaska Juneau January 3, 1959
Arizona Phoenix February 14, 1912

Since the state names are unique, is there any reason not to make that
field the primary key?

Another table has to do with grade school multiplication tables. This
is a simple table something like this:

Factors Answer
1x1 1
2x1 2
2x2 4
3x1 3
3x2 6
3x3 9
...
12x1 12
12x2 24
...
12x12 144

Here again, the Factors field (a text field) is unique. Is there any
reason not to use it as the primary key?

In both cases, the tables are small, so adding another 4-byte field is
no big deal, but I'd like to keep the tables as simple as possible
unless there is some downside.

Thanks

--
Running MS Office 2000 Pro on Win2000

Jul 12 '06 #2

P: n/a
On Wed, 12 Jul 2006 11:48:27 -0500, "Amy Blankenship"
<Am********@magnoliamultimedia.comwrote:
>In my opinion, you're asking for trouble if you ever show the user the
primary key
Why is merely showing the user the primary key a problem?

--
Running MS Office 2000 Pro on Win2000
Jul 12 '06 #3

P: n/a
Most of the reference books recommend autonum primary keys, but the
Access help says that any unique keys will work.

What are the tradeoffs?

I have several tables that have unique fields. Can I use them as
primary keys or should I define an autonum primary key?

One table has information about the 50 states in the US. The table
looks like this:

State Capitol Date Admitted
Alabama Montgomery December 14, 1819
Alaska Juneau January 3, 1959
Arizona Phoenix February 14, 1912

Since the state names are unique, is there any reason not to make
that field the primary key?

Another table has to do with grade school multiplication tables. This
is a simple table something like this:

Factors Answer
1x1 1
2x1 2
2x2 4
3x1 3
3x2 6
3x3 9
...
12x1 12
12x2 24
...
12x12 144

Here again, the Factors field (a text field) is unique. Is there any
reason not to use it as the primary key?

In both cases, the tables are small, so adding another 4-byte field
is no big deal, but I'd like to keep the tables as simple as possible
unless there is some downside.

Thanks
I'd recommend you to take the time to use your favourite search engine
for the terms like "natural vs surrogate primary key". Such search
will
probably list some of the pros and cons, in addition to hours of fun
;-)

Basically, some favours usage of surrogate keys (Autonumber), others
favours natural keys, which represents "things" having a business
meaning, and which can also be a combination of fields. Some (including
me) will use both, based upon the requirements. For state, I'd probably
use the two letter code.

Just be aware - for some this isn't just a matter of preference, it's
religion to a degree thats close to fanatism.

Just be sure that if you decide upon surrogate key (Autonumber), then
remember that this will not ensure the integrity of your data! It will
only ensure that each record has a unique number. Say in a table where
you have a unique field, but you decide to add an Autonumber field for
primary key, you will need to also add a unique index on the "natural
key" field in addition to the primary key index on the Autonumber
field,
else you'll risk dupes.

--
Roy-Vidar
Jul 12 '06 #4

P: n/a
Because by user I mean people other than the developer who might be charged
with maintaining your data. When you show something to that type of user,
you lay it open to being changed. Here's a full discussion of the issue
http://www.dbpd.com/vault/9805xtra.htm

"LurfysMa" <in*****@invalid.invalidwrote in message
news:4n********************************@4ax.com...
On Wed, 12 Jul 2006 11:48:27 -0500, "Amy Blankenship"
<Am********@magnoliamultimedia.comwrote:
>>In my opinion, you're asking for trouble if you ever show the user the
primary key

Why is merely showing the user the primary key a problem?

--
Running MS Office 2000 Pro on Win2000

Jul 12 '06 #5

P: n/a
State Capitol Date Admitted
Alabama Montgomery December 14, 1819
Alaska Juneau January 3, 1959
Arizona Phoenix February 14, 1912

Since the state names are unique, is there any reason not to make that
field the primary key?
Yes, there are several good reasons. First, you might find some spelling
errors. You might come out with a French version. Or, someone wants the
names to be spelled in German, or whatever tickles your fancy. If you use a
autonumber, and then start using a description for the State in place of the
name, then your database can continue to function without modification.

As others mentioned, there is much philosophy and strong views on each side
of the camp (natural keys vs autonumber keys).

My view is that when you relate a table, I simply want the database to

please give me a relation between those two tables I specify. At that
point, I give NOT one hoot about what field is used, and in fact I don't
even want to waste my brain power coming up with a field to create the
relaton. I want a one to many relaton. What you do after that is your
business!!

Here is my rant on this subject. It also explains why you don't every want
to expose the autonumber to the end user.

Be forewarned...this is a old post..and is a rant..but, it gives you the
idea of how much fervor can go into the subject...

----------------

Why would you EVE"R care what id ms-access uses for the relation?

Do you care what memory segment word gets loaded into? Do you
care if it is memory segment 32, or 8192?

Are you now to ask users with a prompt as to what memory locaton that your
word document will load into? Who cares..that junk is for comptuers to deal
with...not humans...
Who cares about a number you, and your users will NEVER see?

An autonumber is some mechanistic to generate a number. To you and me, all
we care about is that we have a relation from customers to customers invoice
table. Do we really care, or have to know what number is used?

Really, when word loads into memory, we don't care about the number used for
the memory location. Really, when ms-access has a relation between customers
and the invoice file...again we don't give a hoot about what number is used.
Me, or you never sees the segment number when word loads, and we as users
will never see the autonumber either.

These numbers are NOT for humans to see.....

There is a ZILLION kinds of internal numbers that your computer uses all day
to function. Why do you care what memory segment numbers the computer used
to load ms-access, or ms-word? Why care?

If your folks can see, or use those autonumbers...then that is your problem
with autonumbers.

The real wrong being done here is that users can see, or use the
autonumbers. I mean, do you want ms-word to start showing you the memory
segment numbers it uses to load a document into memory? It would be crazy to
force users to deal with memory segment numbers when using word.

With ms-access, YOU NOW are the software developer. So, just like those
developers who creased word, they don't show users what memory location the
documents load into. You as a developer has a responsibly to NOT LET USERS
see the autonumber.

If you need some number for your users, then you need to write your own
custom code that generates those numbers for human consumption (say, things
like invoice number etc.). You do NOT want to use the invoice number for
relations etc (you still use a internal autonumber, and that way you don't
even care if the invoice has a invoice number, or perhaps you wait a
specified time until a invoice number is given. Either way, you can still
have your relational database function...but behind the scenes it is using a
autonumber).

Your database should not crap out just because you don't have a order number
handy. Who even cares if you enter a order number, or not? Why should your
database stop function if you don't enter a order number? Even if you change
the order number, again..why should your database not work? Maybe you need
to delete the order number? (again, what on planet earth does deleing some
arbitrary number like the order number HAVE ANYTHING to do with building a
functional relation between two tables? How possibility does these two
separate concepts have anything in common?).

You users should NEVER EVER see a autonumber.

You mistake here is to try and let humans see, or even refer to, or use the
autonumber in any way. Autonumbers are NOT to be given meaning by
humans...but ONLY to your software.

Who cars if you have a order number, or not? What does the fact of having a
order number have to do with your database to functionally correctly? If you
want to require that a order number HAS to be entered, then make the order
number a required field, but that simple stupid order number HAS NOTING to
do with setting up a relation between two tables.

Setup your relation between tables with internal numbers, and your database
will JUST WORK REGARDLESS of what fields, and things you decide to store as
data. Do not go and attached some number out of the blue like a stupid order
number to build relations between your tables. Can you imagine if products
like QuickBooks, or even products like ms-word exposed internal numbers used
for relations and other internal numbers as to how the software will
function? Software uses ZILLIONS AND ZILLIONS of internal numbers and
pointers to function.

Now that YOU ARE the software developer, it is up to you to hide these
numbers. You can expose these internal numbers (like autonumbers), but that
is just rude, and just services to torture your users. Hide all the internal
number stuff....every other developer before you did this....

Why expose users to the exhaust pipe of a car when all they want to do is
drive? Software is a machine you build. Build it...make it work, and then
give it to your users. Users do NOT need to know about the kinds of teeth
used in the gears for the car...

So, the two concepts of how relations works is that many of us just believe
that setting up a relation between two tables is a conceptual idea, and HAS
NOTING to do with the data that you need to store. Others would disagree on
this concept...

By the way, there are some STRONG augments for using natural keys. For
example, if I adopt a natural key in my data, then can freely move it
between TWO DIFFERENT systems that respect this approach. (of course, you
have to have those two systems respect that approach!!).
--
Albert D. Kallal (Access MVP)
Edmonton, Alberta Canada
pl*****************@msn.com
http://www.attcanada.net/~kallal.msn


Jul 12 '06 #6

P: n/a
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?

I have several tables that have unique fields. Can I use them as
primary keys or should I define an autonum primary key?

One table has information about the 50 states in the US. The table
looks like this:

State Capitol Date Admitted
Alabama Montgomery December 14, 1819
Alaska Juneau January 3, 1959
Arizona Phoenix February 14, 1912

Since the state names are unique, is there any reason not to make that
field the primary key?

Another table has to do with grade school multiplication tables. This
is a simple table something like this:

Factors Answer
1x1 1
2x1 2
2x2 4
3x1 3
3x2 6
3x3 9
...
12x1 12
12x2 24
...
12x12 144

Here again, the Factors field (a text field) is unique. Is there any
reason not to use it as the primary key?

In both cases, the tables are small, so adding another 4-byte field is
no big deal, but I'd like to keep the tables as simple as possible
unless there is some downside.

Thanks

--
Running MS Office 2000 Pro on Win2000
You could use whatever you want. IMO most of the criticism around this
topic involves autonumbers being used as the primary key, and in
addition, an attempt is made to use these autonumbers as ordinals,
perhaps sequential invoice numbers, rather than merely nominals,
identifiers of the records.
Many developers routinely create an autonumber ID in every table as
they create it. This, IMO, simplifies relationships (they are always
(ID, ID) where ID = ID), and ensures that a unique identifier exists
for each record, without concern for any meaning, duplication or
possible nullability of that identifier (Access forms often are not
updateable unless such an identifier exists).
But if one has the concepts and skill, other primary keys are fine. Of
course, many may not have the skill, and those who do will often choose
autonumbers to standardize their approach to this matter.
I use autonumbers. There are sufficient things to be planned and
decided about db design without including ... what will my primary keys
look like.

BTW, some think of Primary Key as something "special". A primary key is
simply the first created non-nullable unique index. Designating an
index as primary will move it to position one (or return an error). We
could easily do away with this term; I worked with indexes ( a thousand
times more powerful and useful than JET or SQL-Server indexes) for many
many years in the X-Base world without ever hearing it and I find no
particular value in its availability.

Jul 12 '06 #7

P: n/a
rkc
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?
The tradeoff is that some people will think you're an
idiot if you use them and some people will think you're
an idiot if you don't.

As long as you understand that adding an autonumber
as a primary key has nothing to do with the normalization
process I think they are just fine.
Jul 12 '06 #8

P: n/a
RoyVidar <ro*************@yahoo.nowrote:
>Just be aware - for some this isn't just a matter of preference, it's
religion to a degree thats close to fanatism.
<chuckle>

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm
Jul 12 '06 #9

P: n/a
Per LurfysMa:
>What are the tradeoffs?
Two tradeoffs in favor of surrogate keys that I haven't heard mentioned yet:
------------------------------------------------------------------
1) What looked unique yesterday may turn out not to be today.
U.S. social security number being a case in point. You'd *think*
that there won't be any dupes, but over the long run there will be.
For instance in a charitable endowment environment, the same SSN may
identify both a person and a legal entity that manages money - and
if people and legal entities have the same properties in that app,
you'll run into problems using SSN as a PK. And then there is the
issue of people with bogus SSNs....

2) For me, at least - who has only a limited number of brain cells available -
it's convenient when coming back to maintain an app that I haven't looked
at in a year or more not to have to figure out what natural relationships
exist and how/why. All I have to do deal with is "...ID".
------------------------------------------------------------------
--
PeteCresswell
Jul 13 '06 #10

P: n/a
On Thu, 13 Jul 2006 07:41:09 -0400, "(PeteCresswell)" <x@y.Invalidwrote:
>Per LurfysMa:
>>What are the tradeoffs?

Two tradeoffs in favor of surrogate keys that I haven't heard mentioned yet:
------------------------------------------------------------------
1) What looked unique yesterday may turn out not to be today.
U.S. social security number being a case in point. You'd *think*
that there won't be any dupes, but over the long run there will be.
For instance in a charitable endowment environment, the same SSN may
identify both a person and a legal entity that manages money - and
if people and legal entities have the same properties in that app,
you'll run into problems using SSN as a PK. And then there is the
issue of people with bogus SSNs....

2) For me, at least - who has only a limited number of brain cells available -
it's convenient when coming back to maintain an app that I haven't looked
at in a year or more not to have to figure out what natural relationships
exist and how/why. All I have to do deal with is "...ID".
------------------------------------------------------------------
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.
Jul 13 '06 #11

P: n/a
Hi LurfysMa

Nice discussions.
Every record that has an relation with other records in other tables,
must have an unique identification, that used in all the relations. As
long as the "meaning" of the record stays the same, this identification
stays the same. Whether it is Autonumbering or Random or whatever is
not important, as long as it is unique.

Independant of the the identification is what you choose as Primary
Key. This may be your unique identification, but in fact can be any
combination of any fields, as long as they do not contain null-values.

But if you use a non-Autonumbering Primary Key, be sure that you use
your unique identification in your relations!

HBInc.

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?

I have several tables that have unique fields. Can I use them as
primary keys or should I define an autonum primary key?

One table has information about the 50 states in the US. The table
looks like this:

State Capitol Date Admitted
Alabama Montgomery December 14, 1819
Alaska Juneau January 3, 1959
Arizona Phoenix February 14, 1912

Since the state names are unique, is there any reason not to make that
field the primary key?

Another table has to do with grade school multiplication tables. This
is a simple table something like this:

Factors Answer
1x1 1
2x1 2
2x2 4
3x1 3
3x2 6
3x3 9
...
12x1 12
12x2 24
...
12x12 144

Here again, the Factors field (a text field) is unique. Is there any
reason not to use it as the primary key?

In both cases, the tables are small, so adding another 4-byte field is
no big deal, but I'd like to keep the tables as simple as possible
unless there is some downside.

Thanks

--
Running MS Office 2000 Pro on Win2000
Jul 13 '06 #12

P: n/a
Per polite person:
>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.
I do it by creating additional unique keys - concatenating whatever indexed
need tb concatted.
--
PeteCresswell
Jul 13 '06 #13

P: n/a
polite person <sn**@snippers.comwrote in
news:b1********************************@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?

--
David W. Fenton http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/
Jul 13 '06 #14

P: n/a
David W. Fenton wrote:
polite person <sn**@snippers.comwrote in
news:b1********************************@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.
--
Rick Brandt, Microsoft Access MVP
Email (as appropriate) to...
RBrandt at Hunter dot com
Jul 13 '06 #15

P: n/a
rkc
David W. Fenton wrote:
polite person <sn**@snippers.comwrote in
news:b1********************************@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?
Jul 14 '06 #16

P: n/a
On Fri, 14 Jul 2006 01:07:54 GMT, rkc
<rk*@rochester.yabba.dabba.do.rr.bombwrote:
>David W. Fenton wrote:
>polite person <sn**@snippers.comwrote in
news:b1********************************@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
Jul 14 '06 #17

P: n/a
rkc
LurfysMa wrote:

Hmmm... and just why are YOU here? Are you a feeder or a feedee?
Entertainment.
Jul 14 '06 #18

P: n/a

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.

--

Jul 14 '06 #19

P: n/a
onedaywhen wrote:
The answer, for Access/Jet the PK determines the (non-maintained)
clustered index, the physical ordering on disk.
Can you verify this?

Jul 14 '06 #20

P: n/a
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.

Jul 14 '06 #21

P: n/a

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...b;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.

--

Jul 14 '06 #22

P: n/a

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.

--

Jul 14 '06 #23

P: n/a
"Rick Brandt" <ri*********@hotmail.comwrote in
news:SY******************@newssvr12.news.prodigy.c om:
David W. Fenton wrote:
>polite person <sn**@snippers.comwrote in
news:b1********************************@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/
Jul 14 '06 #24

P: n/a
rkc <rk*@rochester.yabba.dabba.do.rr.bombwrote in
news:K7******************@twister.nyroc.rr.com:
David W. Fenton wrote:
>polite person <sn**@snippers.comwrote in
news:b1********************************@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/
Jul 14 '06 #25

P: n/a
"onedaywhen" <ja**********@xsmail.comwrote in
news:11**********************@h48g2000cwc.googlegr oups.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/
Jul 14 '06 #26

P: n/a

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.

--

Jul 14 '06 #27

P: n/a
David W. Fenton wrote:
"Rick Brandt" <ri*********@hotmail.comwrote in
news:SY******************@newssvr12.news.prodigy.c om:
David W. Fenton wrote:
polite person <sn**@snippers.comwrote in
news:b1********************************@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.
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
Jul 14 '06 #28

P: n/a

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.

--

Jul 14 '06 #29

P: n/a
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.

Jul 14 '06 #30

P: n/a
On Fri, 14 Jul 2006 12:24:59 GMT, "Rick Brandt" <ri*********@hotmail.comwrote:
>David W. Fenton wrote:
>"Rick Brandt" <ri*********@hotmail.comwrote in
news:SY******************@newssvr12.news.prodigy. com:
David W. Fenton wrote:
polite person <sn**@snippers.comwrote in
news:b1********************************@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.

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.
I would let this lapse. David likes to have the last word! :)

Jul 14 '06 #31

P: n/a

Lyle Fairfield wrote:
One can get some of the advantages of clustered indexes by choosing a
meaningful primary key, by compacting ... and, perhaps, by defragging.
We seem to be in agreement here i.e. what to consider when choosing a
PK.
This is a far cry from the convenience and power [of] a clustered index.
The gap doesn't appear that wide to me but I'm willing to learn
otherwise. Details?

Thanks,
Jamie.

--

Jul 14 '06 #32

P: n/a
On Fri, 14 Jul 2006 06:59:48 -0500, "David W. Fenton" <XX*******@dfenton.com.invalidwrote:
>rkc <rk*@rochester.yabba.dabba.do.rr.bombwrote in
news:K7******************@twister.nyroc.rr.com:
>David W. Fenton wrote:
>>polite person <sn**@snippers.comwrote in
news:b1********************************@4ax.co m:

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.
My experience is that most tables in published databases make no effort to prevent duplicate records
in tables with a single artificial key, starting with the customer table in NorthWind.
Jul 14 '06 #33

P: n/a

Jamie Collins wrote:
Lyle Fairfield wrote:
One can get some of the advantages of clustered indexes by choosing a
meaningful primary key, by compacting ... and, perhaps, by defragging.

We seem to be in agreement here i.e. what to consider when choosing a
PK.
This is a far cry from the convenience and power [of] a clustered index.

The gap doesn't appear that wide to me but I'm willing to learn
otherwise. Details?

Thanks,
Jamie.
It appears wide to me. A clustered index in SQL-Server is maintained.
No compacting or defragging is required.
In Jet, if the table/file is small, the performance advantages will,
probably, not be noticed.
If the table/file is large, there will be a penalty (time/resources) in
compacting.
Even if we compact, there is, TTBOMK, no guarantee that the compacting
will use contiguous sectors, although we might hope for that if the
disk is new. Pages containing consecutive (from the pimary key point
of view) might be distant from each other (from the disk's point of
view). Defragging is likely to cure this. But defragging is slow. And
defragging may result in the space after the MDB/E being used by
another file. So that, as soon as we update or insert another record it
may again be remoteness (from the disk's point of view) from records
with "adjacent" primary keys. And to correct this remoteness we may
have to compact and defrag.
In a maintained clustered index all of this (we hope) is planned and
managed by the database engine.
This seems to me to be wide gap.
I understand that with a static database, the gap would be considerably
less.
In general, I agree with you that if one were doing a lot of sql work
based on >= 'Mainwaring; and <= 'Milne' it might, depending on other
needs of the db, it might be efficient to use some primary key that
helped with identifying the records wanted or processed.
I think I have said sufficient about this ...if you reply you shall
have the field to yourself.

Jul 14 '06 #34

P: n/a
polite person <sn**@snippers.comwrote in
news:ii********************************@4ax.com:
My experience is that most tables in published databases make no
effort to prevent duplicate records in tables with a single
artificial key, starting with the customer table in NorthWind.
The sample databases and templates that ship with Access are
terrible in every way. They encourage bad schema design and horrid
UI design and programming practices.

--
David W. Fenton http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/
Jul 14 '06 #35

P: n/a
"Jamie Collins" <ja**********@xsmail.comwrote in
news:11********************@p79g2000cwp.googlegrou ps.com:
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.
Given that with Jet you can only have the one clustered index, I
really think this is a pretty irrelevant consideration. I never use
the BETWEEN operator on anything but date fields, which in the vast
majority of tables could not possibly ever be a candidate for PK
(and very seldom even a candidate for inclusion in a compound
natural key, which wouldn't give you the clustered index benefit,
anyway, unless the date was the first field of the compound key).

So, I just don't see any practical benefit in your pointing out the
performance advantage of the clustered index.

Have you tested SEEKs on non-PK indexes?

--
David W. Fenton http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/
Jul 14 '06 #36

P: n/a
"Jamie Collins" <ja**********@xsmail.comwrote in
news:11*********************@75g2000cwc.googlegrou ps.com:
>
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?) . . .
No. I want to minimize the chance that two users will be editing
data on the same data page.
. . . 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?
Well, either way, it's irrelevant for newly added records before the
database is compacted, since those are all going to land in their
own data page that is not written back in PK order.

As long as you've got records added after the last compact, there's
always going to be some data pages (and index data pages) that are
not in the final order (whether your natural key order or my random
Autonumber order), and thus the performance gain can never be fully
realized in an actively used database.

--
David W. Fenton http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/
Jul 14 '06 #37

P: n/a
On Fri, 14 Jul 2006 09:52:16 -0500, "David W. Fenton" <XX*******@dfenton.com.invalidwrote:
>polite person <sn**@snippers.comwrote in
news:ii********************************@4ax.com :
>My experience is that most tables in published databases make no
effort to prevent duplicate records in tables with a single
artificial key, starting with the customer table in NorthWind.

The sample databases and templates that ship with Access are
terrible in every way. They encourage bad schema design and horrid
UI design and programming practices.
So, if this has been encouraged, why is it wrong to point out what the errors are?

Jul 14 '06 #38

P: n/a

David W. Fenton wrote:
As long as you've got records added after the last compact, there's
always going to be some data pages (and index data pages) that are
not in the final order (whether your natural key order or my random
Autonumber order), and thus the performance gain can never be fully
realized in an actively used database.
The table may not be updated frequently e.g. how often does your
telephone company send you a new paper copy directory, how frequent is
your subscription for a refreshed data set? I don't mean to argue
pointlessly, merely point out that you have to base your PKs (on a
table by table basis) on SQL DML which may or may not include frequent
SQL UPDATE statements.

Jamie.

--

Jul 14 '06 #39

P: n/a

David W. Fenton wrote:
with Jet you can only have the one clustered index
Erm, think it is fundamental that a table can have only one clustered
index, regardless of SQL implementation. Surely a table with two
phyical orders is in fact tow tables!
I never use
the BETWEEN operator on anything but date fields, which in the vast
majority of tables could not possibly ever be a candidate for PK
(and very seldom even a candidate for inclusion in a compound
natural key, which wouldn't give you the clustered index benefit,
anyway, unless the date was the first field of the compound key)
You seem to have talked yourself into seeing my point i.e. make the PK
compound with first your date column followed by a candidate key. This
will favour your BETWEEN constructs.

Jamie.

--

Jul 14 '06 #40

P: n/a
If it's that important to you, Access is probably the wrong database to be
using. If, on the other hand, you use your PK's to establish relationships,
then physical location on disk is unimportant. With cheap fast processors,
labor time of the developer saved by using PK's that make development
efficient is far more cost-effective than any small speed gain realized by
trying to physically order records next to each other. Access is not the
tool of choice for DBA's obsessed with application speed ;-).

"Jamie Collins" <ja**********@xsmail.comwrote in message
news:11**********************@b28g2000cwb.googlegr oups.com...
>
David W. Fenton wrote:
>As long as you've got records added after the last compact, there's
always going to be some data pages (and index data pages) that are
not in the final order (whether your natural key order or my random
Autonumber order), and thus the performance gain can never be fully
realized in an actively used database.

The table may not be updated frequently e.g. how often does your
telephone company send you a new paper copy directory, how frequent is
your subscription for a refreshed data set? I don't mean to argue
pointlessly, merely point out that you have to base your PKs (on a
table by table basis) on SQL DML which may or may not include frequent
SQL UPDATE statements.

Jamie.

--

Jul 14 '06 #41

P: n/a
polite person <sn**@snippers.comwrote in
news:ir********************************@4ax.com:
On Fri, 14 Jul 2006 09:52:16 -0500, "David W. Fenton"
<XX*******@dfenton.com.invalidwrote:
>>polite person <sn**@snippers.comwrote in
news:ii********************************@4ax.co m:
>>My experience is that most tables in published databases make no
effort to prevent duplicate records in tables with a single
artificial key, starting with the customer table in NorthWind.

The sample databases and templates that ship with Access are
terrible in every way. They encourage bad schema design and horrid
UI design and programming practices.

So, if this has been encouraged, why is it wrong to point out what
the errors are?
To whom? In what context?

--
David W. Fenton http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/
Jul 15 '06 #42

P: n/a
"Jamie Collins" <ja**********@xsmail.comwrote in
news:11**********************@i42g2000cwa.googlegr oups.com:
David W. Fenton wrote:
>with Jet you can only have the one clustered index

Erm, think it is fundamental that a table can have only one
clustered index, regardless of SQL implementation. Surely a table
with two phyical orders is in fact tow tables!
Yes. What I should have said was that Jet allows only the PK to be
clustered, not the indexed field of your choice.
>I never use
the BETWEEN operator on anything but date fields, which in the
vast majority of tables could not possibly ever be a candidate
for PK (and very seldom even a candidate for inclusion in a
compound natural key, which wouldn't give you the clustered index
benefit, anyway, unless the date was the first field of the
compound key)

You seem to have talked yourself into seeing my point i.e. make
the PK compound with first your date column followed by a
candidate key. This will favour your BETWEEN constructs.
But it's a nonsensical way to pick PKs. Dates are very seldom going
to be part of a natural key, at least not for very many types of
entities.

I think your suggestion is bloody stupid, as it optimizes something
that very seldom needs further optimization in the first place.

--
David W. Fenton http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/
Jul 15 '06 #43

P: n/a
On Fri, 14 Jul 2006 20:06:45 -0500, "David W. Fenton" <XX*******@dfenton.com.invalidwrote:
>polite person <sn**@snippers.comwrote in
news:ir********************************@4ax.com :
>On Fri, 14 Jul 2006 09:52:16 -0500, "David W. Fenton"
<XX*******@dfenton.com.invalidwrote:
>>>polite person <sn**@snippers.comwrote in
news:ii********************************@4ax.com :

My experience is that most tables in published databases make no
effort to prevent duplicate records in tables with a single
artificial key, starting with the customer table in NorthWind.

The sample databases and templates that ship with Access are
terrible in every way. They encourage bad schema design and horrid
UI design and programming practices.

So, if this has been encouraged, why is it wrong to point out what
the errors are?

To whom? In what context?
To the person who originally asked the question here

Jul 15 '06 #44

P: n/a

Amy Blankenship wrote:
If it's that important to you, Access is probably the wrong database to be
using. If, on the other hand, you use your PK's to establish relationships,
then physical location on disk is unimportant.
Did you read the OP's post? They asked (paraphrasing), if I can use NOT
NULL UNIQUE to define my relationships, what do I need PRIMARY KEY for?
I certainly get you point but I'm trying to address another issue i.e.
what's the difference between NOT NULL UNIQUE and PRIMARY KEY in
Access/Jet to which the answer is the clustered index.

We need to differentiate between data integrity and indexing. I agree
that data integrity is vital, whereas indexing is less significant but
still important.

You should think of PRIMARY KEY as your most powerful index for a
table. If you are ignoring this aspect of PK then you are at best
missing out on some potential gain e.g. better performance, improved
concurrency, etc.

It sounds like you have no need for indexes: that's fine, that's your
choice and if your databases are small you will probably not notice any
difference. However, If you do use regular indexes yet don't consider
the indexing aspect of PRIMARY KEY then maybe it's time to reassess you
indexing strategy.

Jamie.

--

Jul 15 '06 #45

P: n/a

David W. Fenton wrote:
Dates are very seldom going
to be part of a natural key, at least not for very many types of
entities.
It seems I was wrong then and you haven't had the epiphany yet.

Stop thinking in terms of PRIMARY KEY as being your primary key,
candidate key, natural key, etc because you can use NOT NULL UNIQUE for
those purposes. For Jet you must think in terms of PRIMARY KEY meaning
clustered index and nothing else, then choose whatever columns makes
sense in that context.
I think your suggestion is bloody stupid, as it optimizes something
that very seldom needs further optimization in the first place.
The important word there is 'seldom'. If 'optimization' is the *only*
thing that differentiates PRIMARY KEY from NOT NULL UNIQUE then why use
PK for any other purpose?

Jamie.

--

Jul 15 '06 #46

P: n/a
I think YOU need to reread it. The poster asked the
advantages/disadvantages of using natural keys vs. autonumber. The words
not null unique did NOT appear.

In your own words, indexing is less significant than data integrity (and,
presumably, developer time). Therefore, the things that are more
significant should be considered first.

If my Access databases were large enough that indexing were an issue, they
wouldn't be in Access.

OK, I'll leave the church of the not null unique vs. primary key to you.
How many times do you kneel facing Redmond each day,anyway ;-)?

-Amy

"Jamie Collins" <ja**********@xsmail.comwrote in message
news:11**********************@75g2000cwc.googlegro ups.com...
>
Amy Blankenship wrote:
>If it's that important to you, Access is probably the wrong database to
be
using. If, on the other hand, you use your PK's to establish
relationships,
then physical location on disk is unimportant.

Did you read the OP's post? They asked (paraphrasing), if I can use NOT
NULL UNIQUE to define my relationships, what do I need PRIMARY KEY for?
I certainly get you point but I'm trying to address another issue i.e.
what's the difference between NOT NULL UNIQUE and PRIMARY KEY in
Access/Jet to which the answer is the clustered index.

We need to differentiate between data integrity and indexing. I agree
that data integrity is vital, whereas indexing is less significant but
still important.

You should think of PRIMARY KEY as your most powerful index for a
table. If you are ignoring this aspect of PK then you are at best
missing out on some potential gain e.g. better performance, improved
concurrency, etc.

It sounds like you have no need for indexes: that's fine, that's your
choice and if your databases are small you will probably not notice any
difference. However, If you do use regular indexes yet don't consider
the indexing aspect of PRIMARY KEY then maybe it's time to reassess you
indexing strategy.

Jamie.

--

Jul 15 '06 #47

P: n/a
On Sat, 15 Jul 2006 13:44:34 -0500, "Amy Blankenship" <Am********@magnoliamultimedia.comwrote:

<snip>
>
If my Access databases were large enough that indexing were an issue, they
wouldn't be in Access.
<snip>
I don't want to intrude in other people's point scoring but newbies might read this.
Indexing is essential in most Access databases. I think you probably mean "the efficiency of the
indexing."
Also the effectiveness of Access as against other dbs depends on other things besides size, as a
matter of fact Access can be used for pretty big databases.

Jul 15 '06 #48

P: n/a
Amy Blankenship wrote:
If my Access databases were large enough that indexing were an issue, they
wouldn't be in Access.
You have Access Databases without tables or records? Cool!

Jul 15 '06 #49

P: n/a

"polite person" <sn**@snippers.comwrote in message
news:k3********************************@4ax.com...
On Sat, 15 Jul 2006 13:44:34 -0500, "Amy Blankenship"
<Am********@magnoliamultimedia.comwrote:

<snip>
>>
If my Access databases were large enough that indexing were an issue, they
wouldn't be in Access.
<snip>
I don't want to intrude in other people's point scoring but newbies might
read this.
Indexing is essential in most Access databases. I think you probably mean
"the efficiency of the
indexing."
Agreed. However, for most newbies and even many more advanced users, the
indexing Access does on its own is sufficient.
Also the effectiveness of Access as against other dbs depends on other
things besides size, as a
matter of fact Access can be used for pretty big databases.
Sure, but if you're trying to squeeze this kind of minute performance
advantage out, Access isn't the right tool.

Jul 15 '06 #50

115 Replies

This discussion thread is closed

Replies have been disabled for this discussion.