Primary Keys

[LurfysMa]

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

[Tim Marshall]

Amy Blankenship wrote:

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 ;-)?

It is Edgar Codd's legacy to which most of us pay homage, the platform
on which database development takes place is utterly irrelevant.

I haven't followed this thread, but I have seen this post. No offence,
but you've shown a complete and, if you are anything close to a
"professional" developer, shocking lack of understanding of relational
database design and of "Access" by:

1) Indicating indexing is not an issue; and

2) by referring to "Access databases".

As I said, if you consider yourself a database developer, all I can say
is...

wow.
--
Tim http://www.ucs.mun.ca/~tmarshal/
^o<
/#) "Burp-beep, burp-beep, burp-beep?" - Quaker Jake
/^^ "Whatcha doin?" - Ditto "TIM-MAY!!" - Me

[Tim Marshall]

Amy Blankenship wrote:

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.

What irresponsible nonsense. Simply setting up a field with an index is
"squeezing"?. And minute performance? Perhaps with a couple of tables
of 1 to 10 records each... I suggest you do some development in the
real world and see how much difference there is in indexed joins versus
unindexed join fields. It's ridiculously easy to do this in "Access"
compared to say, Oracle. But in either platform, indexing is a simple
and very basic design principle.

No amount of rationalization for piss poor design someone might have
done in the past will change that fact.
--
Tim http://www.ucs.mun.ca/~tmarshal/
^o<
/#) "Burp-beep, burp-beep, burp-beep?" - Quaker Jake
/^^ "Whatcha doin?" - Ditto "TIM-MAY!!" - Me

[David W. Fenton]

polite person <sn**@snippers.comwrote in
news:22********************************@4ax.com:

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

>>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?

To the person who originally asked the question here

The original poster asked:

I have several tables that have unique fields. Can I use them as

primary keys or should I define an autonum primary key?

I read that as saying he/she already had unique indexes on the
candidate keys.

Thus, the advice about unique indexes was unnecessary.

The suggestion was actually made by you in a reply to Pete
Cresswell, who knows perfectly well that unique indexes should be in
place with surrogate keys. Perhaps Pete didn't mention that in his
reply to the original poster precisely because the original post
suggested that it didn't need to be mentioned.

So, it looks to me like the constant citing of this caveat is
something of a knee-jerk reaction on the part of those promoting
natural keys.

Which was my point from the beginning.

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

[David W. Fenton]

"Jamie Collins" <ja**********@xsmail.comwrote in
news:11*********************@m73g2000cwd.googlegro ups.com:

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 you're mis-using your RDBMS if you treat it that way.

>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?

Why would you think I believe that that's the only difference? I
certainly don't think so, and have never suggested as much.

I'm not going to take the time to enumerate the many differences, as
you just seem fixated on one subject, a very impractical and
ridiculous idea, it seems to me.

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

[David W. Fenton]

"Jamie Collins" <ja**********@xsmail.comwrote in
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?

No, you're completely wrong -- that isn't by any stretch of the
imagination the question the OP asked.

The poster simply asked about the pros and cons of surrogate vs.
natural keys.

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

Primary keys are important beyond their indexing and it's bloody
stupid to designate an index as a PK just so you get a clustered
index if it's *not* best candidate for the PK.

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

[David W. Fenton]

"Amy Blankenship" <Am********@magnoliamultimedia.comwrote in
news:On**************@TK2MSFTNGP03.phx.gbl:

. . . if you're trying to squeeze this kind of minute performance
advantage out, Access isn't the right tool.

Oh, give me a break. Jet can handle millions of records just fine,
but without proper indexing, it wouldn't be usable.

Proper indexing is essential in *all* database engines.

And Jet is *not* a toy database in terms of data handling. It only
falls down in comparison to other databases in terms of handling
large numbers of simultaneous users and in terms of the size of the
data store.

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

[polite person]

On Sat, 15 Jul 2006 20:23:05 -0500, "David W. Fenton" <XX*******@dfenton.com.invalidwrote:

>polite person <sn**@snippers.comwrote in
news:22********************************@4ax.com :

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

The original poster asked:

>I have several tables that have unique fields. Can I use them as

primary keys or should I define an autonum primary key?

I read that as saying he/she already had unique indexes on the
candidate keys.

Thus, the advice about unique indexes was unnecessary.

The suggestion was actually made by you in a reply to Pete
Cresswell, who knows perfectly well that unique indexes should be in
place with surrogate keys. Perhaps Pete didn't mention that in his
reply to the original poster precisely because the original post
suggested that it didn't need to be mentioned.

So, it looks to me like the constant citing of this caveat is
something of a knee-jerk reaction on the part of those promoting
natural keys.

Which was my point from the beginning.

In another thread you say
"The poster simply asked about the pros and cons of surrogate vs.
natural keys. "
which is how I read it too. Surely I haven't committed such a great offence?

Actually I am far from an advocate of natural keys, in fact I don't believe they exist. We can't put
real life objects into tables, only their names, which has the problem mentioned by Pete, eg two
things can have the same name without us at the time knowing it but finding out later.

This also applies to things identified by properties. Think of a database of games.

There is a case I suppose for treating an autonumber as part of the data itself, which would allow
us to have two people (for example) about which we hold exactly the same information but which we
know are actually different people.

My remark should have also have been unnecessary because in the relational model (or any model based
on sets) duplicates should not exist - in fact the concept of duplicateness (duplicity?) doesn't
occur, but some dbs allow it both in tables and in result "sets".

[Amy Blankenship]

Whoah, whoah. Hang on. I was referring to the topic of this part of the
thread, which was the use of primary keys that are not unique identifiers of
the record in order to cluster them on disk, NOT simply indexing.

"Tim Marshall" <TI****@PurplePandaChasers.Moertheriumwrote in message
news:e9**********@coranto.ucs.mun.ca...

Amy Blankenship wrote:

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

What irresponsible nonsense. Simply setting up a field with an index is
"squeezing"?. And minute performance? Perhaps with a couple of tables of
1 to 10 records each... I suggest you do some development in the real
world and see how much difference there is in indexed joins versus
unindexed join fields. It's ridiculously easy to do this in "Access"
compared to say, Oracle. But in either platform, indexing is a simple and
very basic design principle.

No amount of rationalization for piss poor design someone might have done
in the past will change that fact.
--
Tim http://www.ucs.mun.ca/~tmarshal/
^o<
/#) "Burp-beep, burp-beep, burp-beep?" - Quaker Jake
/^^ "Whatcha doin?" - Ditto "TIM-MAY!!" - Me

[Amy Blankenship]

Hey, hang on here. I never was referring to simple indexing as the
squeezing of performance. I was referring to the bizarre suggestion of
Jamie Collins that people pick primary keys not for their uniqueness but to
physically cluster the records on disk. This is a practice you, yourself
have taken issue with. Let me completely eliminate any possibility of
misunderstanding here for those unable to take things in context:

If you are so concerned with performance that you are picking your primary
key in order to physically cluster the records, you need to be using a
different database because Access, while a fine database and able to hold up
to pretty stiff requirements, was not build for _that_ type of fine tuning.

Gees!

"David W. Fenton" <XX*******@dfenton.com.invalidwrote in message
news:Xn**********************************@127.0.0. 1...

"Amy Blankenship" <Am********@magnoliamultimedia.comwrote in
news:On**************@TK2MSFTNGP03.phx.gbl:

>. . . if you're trying to squeeze this kind of minute performance
advantage out, Access isn't the right tool.

Oh, give me a break. Jet can handle millions of records just fine,
but without proper indexing, it wouldn't be usable.

Proper indexing is essential in *all* database engines.

And Jet is *not* a toy database in terms of data handling. It only
falls down in comparison to other databases in terms of handling
large numbers of simultaneous users and in terms of the size of the
data store.

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

[Amy Blankenship]

"Tim Marshall" <TI****@PurplePandaChasers.Moertheriumwrote in message
news:e9**********@coranto.ucs.mun.ca...

Amy Blankenship wrote:

>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 ;-)?

It is Edgar Codd's legacy to which most of us pay homage, the platform on
which database development takes place is utterly irrelevant.

I haven't followed this thread, but I have seen this post. No offence,
but you've shown a complete and, if you are anything close to a
"professional" developer, shocking lack of understanding of relational
database design and of "Access" by:

1) Indicating indexing is not an issue; and

2) by referring to "Access databases".

So, you're saying that multiple files created in access are not Access
databases? What do you call them then...?

Just wondering...

[David W. Fenton]

"Amy Blankenship" <Am********@magnoliamultimedia.comwrote in
news:e3*************@TK2MSFTNGP05.phx.gbl:

"David W. Fenton" <XX*******@dfenton.com.invalidwrote in message
news:Xn**********************************@127.0.0. 1...

>"Amy Blankenship" <Am********@magnoliamultimedia.comwrote in
news:On**************@TK2MSFTNGP03.phx.gbl:

>>. . . if you're trying to squeeze this kind of minute
performance advantage out, Access isn't the right tool.

Oh, give me a break. Jet can handle millions of records just
fine, but without proper indexing, it wouldn't be usable.

Proper indexing is essential in *all* database engines.

And Jet is *not* a toy database in terms of data handling. It
only falls down in comparison to other databases in terms of
handling large numbers of simultaneous users and in terms of the
size of the data store.

Hey, hang on here. I never was referring to simple indexing as
the squeezing of performance. I was referring to the bizarre
suggestion of Jamie Collins that people pick primary keys not for
their uniqueness but to physically cluster the records on disk.
This is a practice you, yourself have taken issue with. Let me
completely eliminate any possibility of misunderstanding here for
those unable to take things in context:

If you are so concerned with performance that you are picking your
primary key in order to physically cluster the records, you need
to be using a different database because Access, while a fine
database and able to hold up to pretty stiff requirements, was not
build for _that_ type of fine tuning.

Gees!

If that's what you meant to say, then that's what you should have
said.

I don't think you've really got grounds to complain, given how far
this nuanced statement of your position is from the original one I
replied to above.

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

[David W. Fenton]

"Amy Blankenship" <Am********@magnoliamultimedia.comwrote in
news:us**************@TK2MSFTNGP04.phx.gbl:

Whoah, whoah. Hang on. I was referring to the topic of this part
of the thread, which was the use of primary keys that are not
unique identifiers of the record in order to cluster them on disk,
NOT simply indexing.

Um, how do you set a non-unique index as a PK? The index has to be
unique to qualify as a PK, however artificially you've created it.

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

[David W. Fenton]

"Amy Blankenship" <Am********@magnoliamultimedia.comwrote in
news:uO**************@TK2MSFTNGP05.phx.gbl:

"Tim Marshall" <TI****@PurplePandaChasers.Moertheriumwrote in
message news:e9**********@coranto.ucs.mun.ca...

>Amy Blankenship wrote:

>>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 ;-)?

It is Edgar Codd's legacy to which most of us pay homage, the
platform on which database development takes place is utterly
irrelevant.

I haven't followed this thread, but I have seen this post. No
offence, but you've shown a complete and, if you are anything
close to a "professional" developer, shocking lack of
understanding of relational database design and of "Access" by:

1) Indicating indexing is not an issue; and

2) by referring to "Access databases".

So, you're saying that multiple files created in access are not
Access databases? What do you call them then...?

Just wondering...

We're not talking about Access databases here, but Jet.

D'oh.

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

[Amy Blankenship]

See, that right there argues against his point. You couldn't use a date,
for instance, because that might not be unique but you might then want to
use it as a condition in a BETWEEN clause.

"David W. Fenton" <XX*******@dfenton.com.invalidwrote in message
news:Xn**********************************@127.0.0. 1...

"Amy Blankenship" <Am********@magnoliamultimedia.comwrote in
news:us**************@TK2MSFTNGP04.phx.gbl:

>Whoah, whoah. Hang on. I was referring to the topic of this part
of the thread, which was the use of primary keys that are not
unique identifiers of the record in order to cluster them on disk,
NOT simply indexing.

Um, how do you set a non-unique index as a PK? The index has to be
unique to qualify as a PK, however artificially you've created it.

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

[Amy Blankenship]

"David W. Fenton" <XX*******@dfenton.com.invalidwrote in message
news:Xn**********************************@127.0.0. 1...

"Amy Blankenship" <Am********@magnoliamultimedia.comwrote in
news:uO**************@TK2MSFTNGP05.phx.gbl:

>"Tim Marshall" <TI****@PurplePandaChasers.Moertheriumwrote in
message news:e9**********@coranto.ucs.mun.ca...

>>Amy Blankenship wrote:

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 ;-)?

It is Edgar Codd's legacy to which most of us pay homage, the
platform on which database development takes place is utterly
irrelevant.

I haven't followed this thread, but I have seen this post. No
offence, but you've shown a complete and, if you are anything
close to a "professional" developer, shocking lack of
understanding of relational database design and of "Access" by:

1) Indicating indexing is not an issue; and

2) by referring to "Access databases".

So, you're saying that multiple files created in access are not
Access databases? What do you call them then...?

Just wondering...

We're not talking about Access databases here, but Jet.

Now this is just plain silly. The access file is actually a complete
application that contains, among other things, tables that _can be_
accessed by the Jet engine, and are accessed from inside the Access
application (with forms, reports, and module). However, you can also access
the database tables with other engines when calling the *Access Database*
from outside the Access application. In all probability, you could probably
call an Access Database file from another Access Database file using a
different engine, though I have not tried it. It's on my to-do list.

Saying that "Access Database" is not a valid way to refer to a file created
in the Access application containing tables that _can_ be accessed by Jet is
in my opinion a bit of a stretch. However, it does bring up an interesting
question, one I don't claim to know the answer to: if you set up your
indexes from within Access, but then you call the file with another driver,
how do the indexes behave?

-Amy

[Rick Brandt]

Amy Blankenship wrote:

"David W. Fenton" <XX*******@dfenton.com.invalidwrote in message
news:Xn**********************************@127.0.0. 1...

We're not talking about Access databases here, but Jet.

Now this is just plain silly. The access file is actually a complete
application that contains, among other things, tables that _can be_
accessed by the Jet engine, and are accessed from inside the Access
application (with forms, reports, and module). However, you can also
access the database tables with other engines when calling the
*Access Database* from outside the Access application. In all
probability, you could probably call an Access Database file from
another Access Database file using a different engine, though I have
not tried it. It's on my to-do list.
Saying that "Access Database" is not a valid way to refer to a file
created in the Access application containing tables that _can_ be
accessed by Jet is in my opinion a bit of a stretch. However, it
does bring up an interesting question, one I don't claim to know the
answer to: if you set up your indexes from within Access, but then
you call the file with another driver, how do the indexes behave?

-Amy

I believe David's point is that one can use other programming environments like
VB to create an MDB containing tables along with an entire application to
interface with that MDB all on a PC that does not even have Access installed.
Would you still call that MDB an "Access Database"?

In addition to being able to use a database stored in an MDB file without using
Access one can also build an interface with Access to a non-Jet database like
SQL Server or Oracle. Would you call those "Access Databases"?

Pedantically, when most people talk about an Access Database, the *database* is
a Jet database and the *application* part is Access. I usually use the term
"Access/Jet" if I mean a database application consisting completely of MDB files
and "Access Application" if I am talking about the front end to any other
database engine.

For me the distinction only matters when the discussion concerns "engine level"
stuff. Discussions about keys, constraints, relationships etc., only make sense
when you are specific about whether the engine is Jet or something else.

As for your question, you would be using Access to create Jet indexes so they
would still apply when you interface with the database from another program.
--
Rick Brandt, Microsoft Access MVP
Email (as appropriate) to...
RBrandt at Hunter dot com

[Larry Linson]

"Rick Brandt" wrote

I believe David's point is that one can use
other programming environments like
VB to create an MDB containing tables
along with an entire application to
interface with that MDB all on a PC that
does not even have Access installed.
Would you still call that MDB an
"Access Database"?

Many do, including Microsoft.

Larry

[polite person]

On Sun, 16 Jul 2006 21:19:53 GMT, "Larry Linson" <bo*****@localhost.notwrote:

>"Rick Brandt" wrote

I believe David's point is that one can use
other programming environments like
VB to create an MDB containing tables
along with an entire application to
interface with that MDB all on a PC that
does not even have Access installed.
Would you still call that MDB an
"Access Database"?

Many do, including Microsoft.

Larry

If you create a word document using open office it is still a word document. Or is it?
My god these threads could go on forever!

[Lyle Fairfield]

polite person wrote:

If you create a word document using open office it is still a word document. Or is it?
My god these threads could go on forever!

In the olden days an mdb file was a JET file. If it were created in VB
3's (and others) utility it would not have a reference to Access ...
someone who didn't know about Access might not even guess there was a
close relationship. Since Access objects have been stored in blobs I
don't know whether or not this is still pertinent and I'm not sure
about such things existing any more but my guess is that they do ... or
can. It would be kinda delusionary to call such a file an Access DB?

[Amy Blankenship]

"Rick Brandt" <ri*********@hotmail.comwrote in message
news:Cs********************@newssvr21.news.prodigy .com...

Amy Blankenship wrote:

>"David W. Fenton" <XX*******@dfenton.com.invalidwrote in message
news:Xn**********************************@127.0.0 .1...

>
We're not talking about Access databases here, but Jet.

Now this is just plain silly. The access file is actually a complete
application that contains, among other things, tables that _can be_
accessed by the Jet engine, and are accessed from inside the Access
application (with forms, reports, and module). However, you can also
access the database tables with other engines when calling the
*Access Database* from outside the Access application. In all
probability, you could probably call an Access Database file from
another Access Database file using a different engine, though I have
not tried it. It's on my to-do list.
Saying that "Access Database" is not a valid way to refer to a file
created in the Access application containing tables that _can_ be
accessed by Jet is in my opinion a bit of a stretch. However, it
does bring up an interesting question, one I don't claim to know the
answer to: if you set up your indexes from within Access, but then
you call the file with another driver, how do the indexes behave?

-Amy

I believe David's point is that one can use other programming environments
like VB to create an MDB containing tables along with an entire
application to interface with that MDB all on a PC that does not even have
Access installed. Would you still call that MDB an "Access Database"?

Absolutely. That's how I use most Access databases I create.

In addition to being able to use a database stored in an MDB file without
using Access one can also build an interface with Access to a non-Jet
database like SQL Server or Oracle. Would you call those "Access
Databases"?

Depending on who I am talking to about it. For most people, probably. Most
people I might need to talk to about it wouldn't understand the distinction,
orcare.

>
Pedantically, when most people talk about an Access Database, the
*database* is a Jet database and the *application* part is Access. I
usually use the term "Access/Jet" if I mean a database application
consisting completely of MDB files and "Access Application" if I am
talking about the front end to any other database engine.

For me the distinction only matters when the discussion concerns "engine
level" stuff. Discussions about keys, constraints, relationships etc.,
only make sense when you are specific about whether the engine is Jet or
something else.

Again, depends on who you're talking to. Most people understand "Access
database" but couldn't care less what the Jet engine does.

As for your question, you would be using Access to create Jet indexes so
they would still apply when you interface with the database from another
program.

Cool.

[cjb_kjb]

Just a few points on this topic:

Regarding clustering indexes: Pity MS made such a poor choice to
cluster on the Primary key. I agree a surrogate key is a poor choice
for a clustering index but so too are most unique indexes. For
instance in a client database Name might make a good clustering index
but it can never be unique. If you concatenate name with a multitude
of other fields as the PK then all these fields need to be replicated
in any child/grandchild..... relationships. Waste of space and just
try writing the SQL!!

The main argument regarding the use of surrogate keys that doesn't seem
to have been touched on (although I did only skim over a lot that has
been written) is their pros and cons regarding relationships.

If we have table Employee and Name is a data field that is unique so is
a good candidate PK. (I know name will never be unique but just for
this example we'll imagine it is) So PK of Employee can be Name or we
can introduce a surrogate key Emp.Id.

Table Emp_Posn is a child of Employee so whatever is the key of
Employee will be a foreign key in Emp-Posn. The PK of Emp-Posn can be
the concatenation of Name and Job_Title or if surrogate keys are used
Emp_Posn.Id

If you use the data fields as PKs then when you read Emp_Posn and you
want to know the name of the Employee it relates to, it's already in
Emp-Posn so you dont have to do a join with Employee. If you used
Emp.Id as the PK of Employee then you would have to do a join with
Employee to get the employee's name.

So a benefit of using data fields as PKs can save some joins.

Now consider a child of EMP_Posn, lets say Emp_Posn_Pay.
example: Bill Smith, Forklift Driver, 1/3/2005, $120
Bill Smith, Forklift Driver, 1/3/2006, $150

So primary key of this table could be Name, Job_Title, IncrDate.

Similarly when reading this table you get name and job title without
any joins.

Now if we had used surrogate keys:
PK of Employee would be Emp.Id
PK or Emp_Posn would be Emp_Posn.Id (Not emp>Id + Emp_Posn.Id) because
Emp_Posn.Id is a completely separate autonumber.
PK of Emp_Posn_Pay would be Emp_Posn_Pay.Id

Now if we have to join all 3 tables:
with the data fields used as PKs the SQL would be: Select....from
Employee, Emp_Posn, Emp_Posn_Pay where Emp.Name = Emp_Posn.name and
Emp_Posn.name = Emp_Posn_Pay.name and Emp_Posn.Job_Title =
Emp_Posn_Pay.JobTitle

gets a bit hard to read - anything hard to read is easy to make errors
in!!

If surrogate keys are used:
Select....from Employee, Emp_Posn, Emp_Posn_Pay where Emp.Id =
Emp_Posn.EmpId and Emp_Posn.Id = Emp_posn_Pay.EmpPosnId

So argument for surrogate keys are:
Guarantee Uniqueness
Simplifies SQL

against:
May have to do some extra joins (because the data isnt in child
tables).
Just as an aside - my experience with Access is that it doesn't handle
concatenated keys in SQL very well. I'd always use surrogate keys in
Access except maybe in very small simple tables. An example where I
wouldn't use a surrogate key: I have a table in an application called
Severity which is a number assigned to indicate the severity of a
problem. The numbers are 1, 2, 3, 4. That's it. A table with those
four numbers (nothing else) in separate records of a table. Reason for
the table - to enforce integrity of the data on forms where severity
has to be specified. Could have done it with values specified in a
combo box instead of the combo box looking up a table but if some smart
arse wants to introduce a Sev 5 then I only have to add an additional
record to the table; - if it's hard coded I have to change the code
and redistribute the application.

[David W. Fenton]

"Amy Blankenship" <Am********@magnoliamultimedia.comwrote in
news:#z**************@TK2MSFTNGP05.phx.gbl:

"David W. Fenton" <XX*******@dfenton.com.invalidwrote in message
news:Xn**********************************@127.0.0. 1...

>"Amy Blankenship" <Am********@magnoliamultimedia.comwrote in
news:us**************@TK2MSFTNGP04.phx.gbl:

>>Whoah, whoah. Hang on. I was referring to the topic of this
part of the thread, which was the use of primary keys that are
not unique identifiers of the record in order to cluster them on
disk, NOT simply indexing.

Um, how do you set a non-unique index as a PK? The index has to
be unique to qualify as a PK, however artificially you've created
it.

See, that right there argues against his point. You couldn't use
a date, for instance, because that might not be unique but you
might then want to use it as a condition in a BETWEEN clause.

Er, ever heard of compound keys?

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

[David W. Fenton]

"Amy Blankenship" <Am********@magnoliamultimedia.comwrote in
news:OP*************@TK2MSFTNGP05.phx.gbl:

>
"David W. Fenton" <XX*******@dfenton.com.invalidwrote in message
news:Xn**********************************@127.0.0. 1...

>"Amy Blankenship" <Am********@magnoliamultimedia.comwrote in
news:uO**************@TK2MSFTNGP05.phx.gbl:

>>"Tim Marshall" <TI****@PurplePandaChasers.Moertheriumwrote in
message news:e9**********@coranto.ucs.mun.ca...
Amy Blankenship wrote:

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 ;-)?

It is Edgar Codd's legacy to which most of us pay homage, the
platform on which database development takes place is utterly
irrelevant.

I haven't followed this thread, but I have seen this post. No
offence, but you've shown a complete and, if you are anything
close to a "professional" developer, shocking lack of
understanding of relational database design and of "Access" by:

1) Indicating indexing is not an issue; and

2) by referring to "Access databases".

So, you're saying that multiple files created in access are not
Access databases? What do you call them then...?

Just wondering...

We're not talking about Access databases here, but Jet.

Now this is just plain silly. . . .

No, it's not the least bit silly. Discussion in this newsgroup would
go much more smoothly if people maintained the distinction between
Jet and Access in their posts. Often, it's necessary to sort out
what someone is trying to do and whether they are asking about an
Access problem or a Jet problem.

. . . The access file is actually a complete
application that contains, among other things, tables that _can
be_ accessed by the Jet engine, . . .

But indexes RI are *not* an Access feauture, but a Jet features. On
that level we are talking purely about Jet and not about Access at
all.

. . . and are accessed from inside the Access
application (with forms, reports, and module). However, you can
also access the database tables with other engines when calling
the *Access Database* from outside the Access application. In all
probability, you could probably call an Access Database file from
another Access Database file using a different engine, though I
have not tried it. It's on my to-do list.

Who gives a rat's ass?

Saying that "Access Database" is not a valid way to refer to a
file created in the Access application containing tables that
_can_ be accessed by Jet is in my opinion a bit of a stretch. . .
.

Well, the reason you're "upset" is because you have completely
misunderstood the point.

The subject of this thread is *not* an Access issue -- it has
nothing to do with the properties of Jet databases created by Access
that are specific to Access. It is entirely about properties of the
Jet database engine.

. . . However, it does bring up an interesting
question, one I don't claim to know the answer to: if you set up
your indexes from within Access, but then you call the file with
another driver, how do the indexes behave?

Well, d'oh. Through Jet. And only the data is available.

D'oh.

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

[David W. Fenton]

"Larry Linson" <bo*****@localhost.notwrote in
news:Z3yug.5319$k31.3923@trnddc06:

"Rick Brandt" wrote

I believe David's point is that one can use
other programming environments like
VB to create an MDB containing tables
along with an entire application to
interface with that MDB all on a PC that
does not even have Access installed.
Would you still call that MDB an
"Access Database"?

Many do, including Microsoft.

And that's a bad thing.

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

[David W. Fenton]

"Amy Blankenship" <Am********@magnoliamultimedia.comwrote in
news:#f**************@TK2MSFTNGP05.phx.gbl:

Most
people I might need to talk to about it wouldn't understand the
distinction, orcare.

You're not talking to those people when you post in this newsgroup.

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

[David W. Fenton]

"Amy Blankenship" <Am********@magnoliamultimedia.comwrote in
news:#f**************@TK2MSFTNGP05.phx.gbl:

Most people understand "Access
database" but couldn't care less what the Jet engine does.

Even when talking about PKs and RI? They may be *ignorant* and not
know that there's a distinction between Access and the Jet database
engine, but they still should *care* about the distinction. They
just haven't realized it yet.

Much like you, apparently.

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

[David W. Fenton]

"cjb_kjb" <cj*****@hotmail.comwrote in
news:11**********************@h48g2000cwc.googlegr oups.com:

The main argument regarding the use of surrogate keys that doesn't
seem to have been touched on (although I did only skim over a lot
that has been written) is their pros and cons regarding
relationships.

This is not by any stretch of the imagination the only time this
topic has been discussed in this newsgroup. I assume that most
people felt it wasn't worth rehashing the whole subject, and just
addressed the indexing issue, as it was raised early on in the
thread.

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

[Amy Blankenship]

"David W. Fenton" <XX*******@dfenton.com.invalidwrote in message
news:Xn**********************************@127.0.0. 1...

"Amy Blankenship" <Am********@magnoliamultimedia.comwrote in
news:OP*************@TK2MSFTNGP05.phx.gbl:

<snip>

>Now this is just plain silly. . . .

No, it's not the least bit silly. Discussion in this newsgroup would
go much more smoothly if people maintained the distinction between
Jet and Access in their posts. Often, it's necessary to sort out
what someone is trying to do and whether they are asking about an
Access problem or a Jet problem.

But in this case someone was asking about autonumber vs natural key. Hardly
an issue where fine semantical distinctions are important.

>. . . The access file is actually a complete
application that contains, among other things, tables that _can
be_ accessed by the Jet engine, . . .

But indexes RI are *not* an Access feauture, but a Jet features. On
that level we are talking purely about Jet and not about Access at
all.

I don't think you can really talk about mdb files and leave Access
completely out of it.

>. . . and are accessed from inside the Access
application (with forms, reports, and module). However, you can
also access the database tables with other engines when calling
the *Access Database* from outside the Access application. In all
probability, you could probably call an Access Database file from
another Access Database file using a different engine, though I
have not tried it. It's on my to-do list.

Who gives a rat's ass?

Well, since we're discussing fine esoteric points, I expect everyone cares.
Since they care about every hair splitting semantical detail, apparently.

>Saying that "Access Database" is not a valid way to refer to a
file created in the Access application containing tables that
_can_ be accessed by Jet is in my opinion a bit of a stretch. . .
.

Well, the reason you're "upset" is because you have completely
misunderstood the point.

If I'm "upset" at all, it is because someone said that using the term
"Access databases" was some sort of indicator that I don't know what I am
talking about. That was very unprofessional on his part and unneccessary to
the discussion. IME, though, people who find it necessary to make that kind
of allegation have few skills of their own and feel it props up their own
reputation to try to tear down others'. Luckily, many if not most people
are astute enough to recognize that going around trying to tear others down
is at the least an indicaor of low self esteem.

One might conjecture, though, that someone who feels the need to respond to
*one* post several times is, in face, "upset", for whatever reason.

The subject of this thread is *not* an Access issue -- it has
nothing to do with the properties of Jet databases created by Access
that are specific to Access. It is entirely about properties of the
Jet database engine.

But the fact is that the database was created with Access (at least
hypothetically--none of this discussion seems to relate to an actual file).
Therefore it is an Access issue, whether it deals with part of Access or all
of Access.

>. . . However, it does bring up an interesting
question, one I don't claim to know the answer to: if you set up
your indexes from within Access, but then you call the file with
another driver, how do the indexes behave?

Well, d'oh. Through Jet. And only the data is available.

That's so informative. Very specific...

:p

[Jamie Collins]

David W. Fenton wrote:

If 'optimization' is the
*only* thing that differentiates PRIMARY KEY from NOT NULL UNIQUE
then why use PK for any other purpose?

Why would you think I believe that that's the only difference? I
certainly don't think so, and have never suggested as much.

I'm not going to take the time to enumerate the many differences, as
you just seem fixated on one subject, a very impractical and
ridiculous idea, it seems to me.

Please do take the time take the time to enumerate the differences FOR
JET, otherwise my short list will stand:

1) PK determines the (non-maintained) clustered index.
2) NULLs are not permitted in a PK column, which is unnecessarily
restrictive for compound PKs IMO, making NOT NULL UNIQUE more useful
that PK in Declarative Referential Integrity (DRI) - if you don't
understand the point, I can post an example.
3) Erm...
4) That's it!

If we were to expand the list FOR ACCESS:

1) It makes the PK column(s) bold in the 'Relationships' diagram.
2) Erm...
3) I can't think of anything else but no doubt there are others;
whether the collective Access benefits outweigh the loss of Jet
benefits is another matter...

Jamie.

--

[Jamie Collins]

David W. Fenton wrote:

Um, how do you set a non-unique index as a PK? The index has to be
unique to qualify as a PK, however artificially you've created it.

We seem to have covered this down thread - in fact, I think you
answered you own question - but just to be clear: you put the
non-unique column(s) first and a unique index next. It is often the
case of merely choosing the order of your natural key columns carefully
e.g. if I wanted to cluster on dates for this simply table (largely
ignoring data integrity constraints for simplicity):

CREATE TABLE SalariesHistory (
employee_number CHAR(10) NOT NULL,
start_date DATETIME DEFAULT NOW() NOT NULL,
end_date DATETIME,
salary_amount DECIMAL(15,4) NOT NULL,
CONSTRAINT SalariesHistory__natural_key_and_clustered_index
PRIMARY KEY (start_date, employee_number),
CONSTRAINT SalariesHistory__candidate_key
UNIQUE (end_date, employee_number)
);

and if I wanted to cluster on employee_number then I'd reverse the
columns:

PRIMARY KEY (employee_number, start_date)

As you favour an autonumber (ID) 'uniqueifier' (we'll continue to
disagree as to whether this is a true surrogate) then append this to
the end e.g.

CREATE TABLE SalariesHistory (
ID INTEGER IDENTITY(1, 1) NOT NULL,
employee_number CHAR(10) NOT NULL,
start_date DATETIME DEFAULT NOW() NOT NULL,
end_date DATETIME,
salary_amount DECIMAL(15,4) NOT NULL,
CONSTRAINT SalariesHistory__uniqueifier
UNIQUE (ID),
CONSTRAINT SalariesHistory__natural_key
UNIQUE (start_date, employee_number),
CONSTRAINT SalariesHistory__candidate_key
UNIQUE (end_date, employee_number),
CONSTRAINT SalariesHistory__clustered_index
PRIMARY KEY (start_date, ID)
);

to satisfy the conditions of good clustering and uniqueness (in that
order).

Note that

CONSTRAINT SalariesHistory__candidate_key
UNIQUE (end_date, employee_number),

above is an example of a candidate key which cannot be a PK in Jet
because the end_date colum is nullable, however the unique constraint
is still required for data integrity purposes i.e. a NULL end_date
indicates the current salary period and each employee can only have one
current salary period.

Jamie.

--

[Jamie Collins]

Amy Blankenship wrote:

I think YOU need to reread it. The poster asked the
advantages/disadvantages of using natural keys vs. autonumber.

On re-reading I admit that is one interpretation. However, that
discussion is done to death on a regular basis and it quite boring.
Fortuitous, then, but I didn't intentionally go OT.

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.

Sure, the problem is that for you first is also last <g>. If you are
going the extra mile you may as well think holistically, it may save
some work. For example, first design your table:

CREATE TABLE Directory (
telephone_number VARCHAR(20) NOT NULL,
last_name VARCHAR(35) NOT NULL,
initial VARCHAR(9) NOT NULL
)
;

Next, determine your candidate keys:

ALTER TABLE Directory ADD
CONSTRAINT Directory__natural_key
PRIMARY KEY (telephone_number)
;

You admit you usually stop at this point. However, if you did continue
to consider the clustered index and decided to cluster on last_name you
would have to first undo your previous step:

ALTER TABLE Directory DROP
CONSTRAINT Directory__natural_key
;

ALTER TABLE Directory ADD
CONSTRAINT Directory__natural_key
UNQIUE (telephone_number)
;
ALTER TABLE Directory ADD
CONSTRAINT Directory__clustered_index
PRIMARY KEY (last_name, telephone_number)
;

So you may as well determine keys and the clustered index all in one
go!

Jamie.

--

[Terry Kreft]

He, he, he, "Microsoft" and "Nomenclature" whenever I think of those two
words together it makes me laugh.
--

Terry Kreft
"Larry Linson" <bo*****@localhost.notwrote in message
news:Z3yug.5319$k31.3923@trnddc06...

"Rick Brandt" wrote

I believe David's point is that one can use
other programming environments like
VB to create an MDB containing tables
along with an entire application to
interface with that MDB all on a PC that
does not even have Access installed.
Would you still call that MDB an
"Access Database"?

Many do, including Microsoft.

Larry

[David W. Fenton]

"Amy Blankenship" <Am********@magnoliamultimedia.comwrote in
news:OR**************@TK2MSFTNGP03.phx.gbl:

>
"David W. Fenton" <XX*******@dfenton.com.invalidwrote in message
news:Xn**********************************@127.0.0. 1...

>"Amy Blankenship" <Am********@magnoliamultimedia.comwrote in
news:OP*************@TK2MSFTNGP05.phx.gbl:

<snip>

>>Now this is just plain silly. . . .

No, it's not the least bit silly. Discussion in this newsgroup
would go much more smoothly if people maintained the distinction
between Jet and Access in their posts. Often, it's necessary to
sort out what someone is trying to do and whether they are asking
about an Access problem or a Jet problem.

But in this case someone was asking about autonumber vs natural
key. Hardly an issue where fine semantical distinctions are
important.

They aren't?

>>. . . The access file is actually a complete
application that contains, among other things, tables that _can
be_ accessed by the Jet engine, . . .

But indexes RI are *not* an Access feauture, but a Jet features.
On that level we are talking purely about Jet and not about
Access at all.

I don't think you can really talk about mdb files and leave Access
completely out of it.

Yes, you can. You can use Jet alone to create an MDB file
programatically. Michael Kaplan used to prefer to do this for his
Jet data files because the result was a much slimmer file with
tables that lacked custom Access properties that he didn't need.

So, you're wrong again.

>>. . . and are accessed from inside the Access
application (with forms, reports, and module). However, you can
also access the database tables with other engines when calling
the *Access Database* from outside the Access application. In
all probability, you could probably call an Access Database file
from another Access Database file using a different engine,
though I have not tried it. It's on my to-do list.

Who gives a rat's ass?

Well, since we're discussing fine esoteric points, I expect
everyone cares. Since they care about every hair splitting
semantical detail, apparently.

The issue is completely unrelated to the distinction between Access
and Jet.

>>Saying that "Access Database" is not a valid way to refer to a
file created in the Access application containing tables that
_can_ be accessed by Jet is in my opinion a bit of a stretch. .
. .

Well, the reason you're "upset" is because you have completely
misunderstood the point.

If I'm "upset" at all, it is because someone said that using the
term "Access databases" was some sort of indicator that I don't
know what I am talking about. . . .

When you're talking about purely Jet issue, yes, it's an issue that
you're not thinking clearly or that you're ignorant of the
essential distinction between Access and Jet.

. . . That was very unprofessional on his part and unneccessary to
the discussion. . . .

You're the one who came in and made sweeping pronouncements about
indexing (a Jet issue), yet you've now demonstrated that you don't
understand the most fundamental distinctions about how Access works.

. . . IME, though, people who find it necessary to make that kind
of allegation have few skills of their own and feel it props up
their own reputation to try to tear down others'. Luckily, many
if not most people are astute enough to recognize that going
around trying to tear others down is at the least an indicaor of
low self esteem.

What justification is there for the attitude you threw with your
*wrong* declarations about Access performance and indexes?

One might conjecture, though, that someone who feels the need to
respond to *one* post several times is, in face, "upset", for
whatever reason.

No, not at all. One thing that happens when I read your posts is
that I come across one thing that I think can't be topped in its
stupidity, respond just to that part, and then reading on find out
that you've topped the original absurdity.

>The subject of this thread is *not* an Access issue -- it has
nothing to do with the properties of Jet databases created by
Access that are specific to Access. It is entirely about
properties of the Jet database engine.

But the fact is that the database was created with Access . . .

Which is entirely irrelevant to the question at hand. . .

. . . (at least
hypothetically--none of this discussion seems to relate to an
actual file). Therefore it is an Access issue, whether it deals
with part of Access or all of Access.

No, you're just plain wrong.

>>. . . However, it does bring up an interesting
question, one I don't claim to know the answer to: if you set up
your indexes from within Access, but then you call the file with
another driver, how do the indexes behave?

Well, d'oh. Through Jet. And only the data is available.

That's so informative. Very specific...

You're an idiot, obviously.

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

[David W. Fenton]

"Jamie Collins" <ja**********@xsmail.comwrote in
news:11*********************@h48g2000cwc.googlegro ups.com:

Please do take the time take the time to enumerate the differences
FOR JET

No.

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

[David W. Fenton]

"Jamie Collins" <ja**********@xsmail.comwrote in
news:11**********************@s13g2000cwa.googlegr oups.com:

David W. Fenton wrote:

>Um, how do you set a non-unique index as a PK? The index has to
be unique to qualify as a PK, however artificially you've created
it.

We seem to have covered this down thread - in fact, I think you
answered you own question - but just to be clear: you put the
non-unique column(s) first and a unique index next.

But that's *not* a non-unique index. That's inserting a non-unique
field into a compound key in order to artificially create a unique
key. Since the clustered index is written in the order of the
compound key, if you put the non-unique field first, the index and
data will be written in the order of the first field.

But it's still not a non-unique *index*. It's just a non-unique
field participating in a compound index.

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

[Jamie Collins]

David W. Fenton wrote:

Please do take the time take the time to enumerate the differences
FOR JET

No.

I therefore conclude there are none.

Jamie.

--

[Jamie Collins]

David W. Fenton wrote:

But it's still not a non-unique *index*. It's just a non-unique
field participating in a compound index.

I can't make up my mind whether you are confused, deliberately trying
to confuse the point or just have a confusing way with the English
language <g>.

I think I may have mislead you by this:

....
start_date DATETIME DEFAULT NOW() NOT NULL,
end_date DATETIME,
....

I intended for start_date values to have no time element and for
end_date to be one granule of time before the next contiguous time
duration i.e.

....
start_date DATETIME DEFAULT DATE() NOT NULL,
CONSTRAINT salary_start_date__closed_period
CHECK (
HOUR(start_date) = 0
AND MINUTE(start_date) = 0
AND SECOND(start_date) = 0
),
end_date DATETIME,
CONSTRAINT salary_end_date__open_period
CHECK (
HOUR(end_date) = 23
AND MINUTE(end_date) = 59
AND SECOND(end_date) = 59
), ...

not to mention constraints to ensure non-overlapping periods for the
same employee, contiguous periods, ensuring the salary amount changes
between contiguous periods, etc but I wanted to keep it simple.

I'll try and be clear: my aim is to create a clustered index on
start_date (rather than not creating a not non-unique index on nothing
<vbg>).

To get a clustered index in Jet, I must leverage the PRIMARY KEY
syntax.

Defining a PRIMARY KEY *constraint* causes the engine to create a
(non-maintained) clustered *index*.

My start_date column has no time elements i.e. all start dates commence
at midnight, making it suitable for clustering.

My start_date column is not unique in the table because more than one
employee can start receiving a salary amount effective on the same day.
To use start_date in the PK I need to append (i.e. to the right in the
PK definition) additional column(s) that will 'uniqueify' the
start_date column; this could be an autonumber column (if used) or
employee_number.

Beyond start_date it really doesn't matter which columns are included
as long as the compound is unique; put another way, I don't care that
the engine needs something as a 'tie breaker' for when the start_dates
coincide, I just want them to be clustered together.

I hope you can now understand my point and that any reply is a lot less
confused/confusing than your previous one.

Jamie.

--

[Terry Kreft]

Which says a bit more about you really.

David is not here to spoon feed you or anyone else, if he chooses not to
answer that is his prerogative. For you to make a totally unrelated
conclusion from that shows either fallible logic on your part or a
determination to appear foolish.
--

Terry Kreft
"Jamie Collins" <ja**********@xsmail.comwrote in message
news:11**********************@p79g2000cwp.googlegr oups.com...

>
David W. Fenton wrote:

Please do take the time take the time to enumerate the differences
FOR JET

No.

I therefore conclude there are none.

Jamie.

--

[Jamie Collins]

Terry Kreft wrote:

David is not here to spoon feed you or anyone else,

Hey, plenty of people come here to get spoon fed. Why not me <g>?

if he chooses not to
answer that is his prerogative.

Not replying is one thing but for him to post back to say he is not
going to answer me...?

For you to make a totally unrelated
conclusion from that shows either fallible logic on your part or a
determination to appear foolish.

Terry, I'm merely suspicious of someone who repeatedly takes the time
to say (quote), "I'm not going to take the time to enumerate the many
differences."

I genuinely believe the man is bluffing. You no doubt recall the last
time we had this attitude from him
(http://groups.google.com/group/micro...9225a5a30a2caa)
i.e. he alludes that he 'knows something' but won't deign to post it.

I'm sure everyone knows I'm not afraid of being made to look foolish
<g>.

Which says a bit more about you really.

I hope the message about me is, 'I will assert this position until
someone can show me I'm wrong.'

I don't think there is much credit in saying (paraphrasing), 'I know
you are wrong but I will not attempt to demonstrate it.'

Jamie.

--

[Jamie Collins]

Terry Kreft wrote:

David is not here to spoon feed you or anyone else

Actually, rather than the flippancy of my original reply, I think I
should address that point.

I don't expect to be spoon fed, rather I would like people provide
something substantial (code demonstration, link to a MSDN article, etc)
to back up their assertions. I usually do so up front and always do so
when challenged, and humble pie is often dish of the day.

David has not even put up his short list, let alone tried to make it
stand up.

I'm put in mind of Fermat: "I have a truly marvellous proof of this
proposition which this margin is too narrow to contain."

Jamie.

--

[Terry Kreft]

But that's David's choice whether he supplies that or not, you are free to
make any inference you like from his failure to reply but if you post that
inference then you leave yourself open to challenge. Your concluding remark
was not a reasonable conclusion from David's refusal to answer.
--

Terry Kreft
"Jamie Collins" <ja**********@xsmail.comwrote in message
news:11**********************@h48g2000cwc.googlegr oups.com...

>
Terry Kreft wrote:

<SNIP>

>I would like people provide
something substantial (code demonstration, link to a MSDN article, etc)
to back up their assertions. I usually do so up front and always do so
when challenged, and humble pie is often dish of the day.

David has not even put up his short list, let alone tried to make it
stand up.
Jamie.

--

[Tim Marshall]

Jamie Collins wrote:

I genuinely believe the man is bluffing. You no doubt recall the last

Ummm, David Fenton bluffing? He very commendably doesn't stoop to flame
wars, so you must be talking about his Access/Jet/VBA knowledge. In
which case, all due respect to you, Jamie, I'm afraid I'd have to
conclude your belief is erroneous. 8)
--
Tim http://www.ucs.mun.ca/~tmarshal/
^o<
/#) "Burp-beep, burp-beep, burp-beep?" - Quaker Jake
/^^ "Whatcha doin?" - Ditto "TIM-MAY!!" - Me

[Larry Linson]

"Jamie Collins" wrote

I genuinely believe the man is bluffing. You
no doubt recall the last time we had this
attitude from him

As Tim has said, "Your belief is erroneous."

David does not "tolerate fools gladly" and his words are sometimes sharper
than I might write, but he "knows his stuff" when it comes to Access and
Jet.

Larry Linson

[David W. Fenton]

"Jamie Collins" <ja**********@xsmail.comwrote in
news:11**********************@m79g2000cwm.googlegr oups.com:

I genuinely believe the man is bluffing. You no doubt recall the
last time we had this attitude from him
(http://groups.google.com/group/micro...ss/msg/499225a
5a30a2caa) i.e. he alludes that he 'knows something' but won't
deign to post it.

MessageIDs, please.

Otherwise, retract the allegation.

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

[David W. Fenton]

"Terry Kreft" <te*********@mps.co.ukwrote in
news:ui**************@TK2MSFTNGP05.phx.gbl:

But that's David's choice whether he supplies that or not, you are
free to make any inference you like from his failure to reply but
if you post that inference then you leave yourself open to
challenge. Your concluding remark was not a reasonable conclusion
from David's refusal to answer.

The reason I won't is that it would be rehashing material that's
been posted every time the topic of natural vs. surrogate keys has
come up. I don't see any point in posting something that would just
duplicate information that's been posted here dozens of times.

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

[David W. Fenton]

"Jamie Collins" <ja**********@xsmail.comwrote in
news:11**********************@m79g2000cwm.googlegr oups.com:

To get a clustered index in Jet, I must leverage the PRIMARY KEY
syntax.

You mis-spelled "mis-use PK syntax."

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

[David W. Fenton]

"Jamie Collins" <ja**********@xsmail.comwrote in
news:11**********************@m79g2000cwm.googlegr oups.com:

I hope you can now understand my point and that any reply is a lot
less confused/confusing than your previous one.

I'm done here. You're an idiot.

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

[cjb_kjb]

I do agree David.

The indexing issue is pretty much a red herring. It has little bearing
on the real issues regarding the use of surrogate or natural keys.

The arguments regarding duplicates also has no bearing. In some
situations it's impossible to define a database that identifies
duplicates - that comes back to a decision only humans can make.

For example Name can never be defined as unique in a Customer database.
But we may try to create uniqueness by using a compound key. Eg. Name
+ Address + DateOfBirth. This still does not mean that two entries
with the same Name, Address, DoB are not two different people,
conversely two entries that do not match completely does not mean it's
not the same person.

Duplicates is another issue entirely. Nothing to do with surrogate or
Natural keys.

Primary Keys are ONLY for defining relationships.

If you have a one table database - what value is a primary key - none!
You may want an index that restricts duplicates in a particular field -
that's another issue. (That field may be a candidate for Primary Key
if you want to relate thsi table to another).

The only use for Primary keys is so one can identify which Parent
record Child records relate to. The simplest way to relate records is
with Ids and autonumbering is the simplest method of creating that Id..

[Jamie Collins]

David W. Fenton wrote:

The reason I won't is that it would be rehashing material that's
been posted every time the topic of natural vs. surrogate keys has
come up. I don't see any point in posting something that would just
duplicate information that's been posted here dozens of times.

My question does not relate to natural vs. surrogate keys.

I want to know the difference in Jet between a column constrained as
NOT NULL UNIQUE and PRIMARY KEY. If there is existing subject matter on
this issue, please provide me with a link.

Jamie.

--

[Jamie Collins]

Terry Kreft wrote:

that's David's choice whether he supplies that or not, you are free to
make any inference you like from his failure to reply but if you post that
inference then you leave yourself open to challenge. Your concluding remark
was not a reasonable conclusion from David's refusal to answer.

My intention is to discourage readers from inferring anything from
someone who makes an assertion without reasoning.

Terry, Take another look. Could it be the case that David W. Fenton is
a subtle kind of troll: adds only snippets of information, does not
elaborate, posts intentionally confusing replies, puts more time and
effort into being rude than advancing the debate, calls everyone an
idiot before disappearing? My advice: don't try and do logic with a
troll.

Jamie.

--