A Microsoft Office (Excel, Word) forum. OfficeFrustration

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » OfficeFrustration forum » Microsoft Access » Database Design
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

Data Primary key vs. Artificial (Autonumber) primary key



 
 
Thread Tools Display Modes
  #21  
Old December 5th, 2007, 03:54 PM posted to microsoft.public.access.tablesdbdesign
Jeff Boyce
external usenet poster
 
Posts: 1,555
Default Data Primary key vs. Artificial (Autonumber) primary key

Oh, you are in so much trouble now!

Just because Douglas and I (Jeff) have traditionally male surnames doesn't
mean we're males... (oh wait, we are, never mindg)

Regards

Jeff

"Jamie Collins" wrote in message
...
On Dec 5, 12:51 pm, "Jeff Boyce" -
DISCARD_HYPHEN_TO_END wrote:
I don't recall making the statement you quote me as making (or maybe you
know another Jeff Boyce):


Check for the quote upthread. You were calling yourself 'Douglas J.
Steele' at the time but you didn't fool me.

Seriously, apologies for attributing the quote to the wrong Access
MVP. You guys all look the same to me g.

Jamie.

--


  #22  
Old December 5th, 2007, 03:56 PM posted to microsoft.public.access.tablesdbdesign
Jeff Boyce
external usenet poster
 
Posts: 1,555
Default Data Primary key vs. Artificial (Autonumber) primary key

Thanks! I suspect you're right.

I don't use Autonumbers, ... except as Primary Keys, so they are, by (my)
definition, unique!

I keep forgetting that not everyone shares MY definition!

--
Regards

Jeff Boyce
www.InformationFutures.net

Microsoft Office/Access MVP
http://mvp.support.microsoft.com/

Microsoft IT Academy Program Mentor
http://microsoftitacademy.com/

"Jamie Collins" wrote in message
...
On Dec 4, 9:15 pm, "Jeff Boyce" wrote:

Calling all Jeff Boyces...

BlueClaw setup for Employee table
Employee_ID (autonumber, unique index)


Why? By definition, an Autonumber is supposed to already be unique, so


An Autonumber is supposed to auto-generate a value according to an
algorithm; the choices are increment, random or GUID. Show me the
section of the Jet specification which says Autonumber is supposed to
be unique. If you like I can post some code to demonstrate the fact
that Jet can auto-generate duplicate Autonumber values (hint: you
change increment value from the default value of one to a value very
close to the maximum for Long Integer).

you
wouldn't gain anything by indexing it.


Can you say "data integrity"? what makes you think that values in an
Autonumber column will be unique unless you put a unique index (or
constraint) on it? Again, I can post code to explicitly insert
duplicate values into an Autonumber column if you like.

Jamie.

--


  #23  
Old December 5th, 2007, 04:09 PM posted to microsoft.public.access.tablesdbdesign
Jamie Collins
external usenet poster
 
Posts: 1,705
Default Data Primary key vs. Artificial (Autonumber) primary key

On Dec 5, 2:54 pm, "Jeff Boyce" -
DISCARD_HYPHEN_TO_END wrote:
Just because Douglas and I (Jeff) have traditionally male surnames doesn't
mean we're males... (oh wait, we are, never mindg)


I've also seen the 'traditionally male' beards in the MVP mug
shots ;-)

Jamie.

--

  #24  
Old December 5th, 2007, 05:05 PM posted to microsoft.public.access.tablesdbdesign
Jeff Boyce
external usenet poster
 
Posts: 8,621
Default Data Primary key vs. Artificial (Autonumber) primary key

Facial hair is not restricted to males...

(hence the facination of the "Bearded Lady" in carnivals!)

You are STILL in trouble!

Regards

Jeff Boyce
Microsoft Office/Access MVP

"Jamie Collins" wrote in message
...
On Dec 5, 2:54 pm, "Jeff Boyce" -
DISCARD_HYPHEN_TO_END wrote:
Just because Douglas and I (Jeff) have traditionally male surnames
doesn't
mean we're males... (oh wait, we are, never mindg)


I've also seen the 'traditionally male' beards in the MVP mug
shots ;-)

Jamie.

--



  #25  
Old December 5th, 2007, 05:22 PM posted to microsoft.public.access.tablesdbdesign
Jamie Collins
external usenet poster
 
Posts: 1,705
Default Data Primary key vs. Artificial (Autonumber) primary key

On Dec 5, 4:05 pm, "Jeff Boyce" wrote:
Facial hair is not restricted to males...


....in the set of Access MVPs?

Jamie.

--

  #26  
Old December 5th, 2007, 05:48 PM posted to microsoft.public.access.tablesdbdesign
Dennis
external usenet poster
 
Posts: 1,222
Default Data Primary key vs. Artificial (Autonumber) primary key

I was speaking from a pure theory perspective, not from an Access-specific one.

OLE objects aren't "data" per se; they're, well, OBJECTS. And memo fields
cannot be used as indexes or searched on, so as a key they'd be impossible to
use. In the "old-timey world" one needed to keep one's keys as short as
possible while being meaningful. A key-length over about 15 characters almost
guaranteed terrible application performance. But that was then....

"Jamie Collins" wrote:

On Dec 4, 10:53 pm, Dennis wrote:
ANY data can be used as a Primary Key AS LONG AS you are 100% sure of no
duplication and/or have code in place to prevent such an occurrance.


IIRC a column of type OLEOBJECT cannot be PRIMARY KEY.

Perhaps you meant to say "as long as the engine can be 100% sure of no
duplication"? While I can be sure that I am putting unique values into
a MEMO column, the engine only considers the first 255 characters when
checking a PRIMARY KEY constraint. Also I'd be vary wary of
approximate numeric types (e.g. REAL and FLOAT).

Jamie.

--


  #27  
Old December 5th, 2007, 06:40 PM posted to microsoft.public.access.tablesdbdesign
John W. Vinson
external usenet poster
 
Posts: 18,261
Default Data Primary key vs. Artificial (Autonumber) primary key

On Wed, 5 Dec 2007 04:07:06 -0800 (PST), Jamie Collins
wrote:

"If you're going to create relationships, they will
always be based on the primary key. You cannot change that."

Michael Gramelspacher said (paraphrasing): "I can create a FOREIGN KEY
REFERENCE (a.k.a. Access Relationship with referential integrity
enforced) based on a UNIQUE constraint, which goes against what Jeff
Boyce said about relationships always being based on the primary key."


You're right (about the meaning if not the attribution), Jamie - I
misinterpreted Michael's point.

John W. Vinson [MVP]
  #28  
Old December 5th, 2007, 06:44 PM posted to microsoft.public.access.tablesdbdesign
John W. Vinson
external usenet poster
 
Posts: 18,261
Default Data Primary key vs. Artificial (Autonumber) primary key

On Wed, 5 Dec 2007 05:18:01 -0800, Fred
wrote:

And that means at least occasionally correcting or
changing it.


well... there *is* Cascade Updates to cover that possibility.

But I agree, it's ideal if the primary key is rock solid stable. If it's only
San Andreas faultline rock solid stable, you need to depend on cascade
updates, which do work but have their own problems!

John W. Vinson [MVP]
  #29  
Old December 5th, 2007, 11:11 PM posted to microsoft.public.access.tablesdbdesign
M.
external usenet poster
 
Posts: 18
Default Data Primary key vs. Artificial (Autonumber) primary key

Thanks for this helpful answer.

Is the following summary correct:

The (unique) index that would be used most frequently for sorting or
filtering the table records, should be defined as primary index from a
performance point of view, because the primary index determines the
sequential physical order on harddisk ?

M.

"Jamie Collins" wrote:

On Dec 4, 9:56 pm, M. wrote:
My main question is: are there negative aspects associated with using a
primary key based on data fields versus using a primary key based on an
artificial primary key as generated with an autonumber field? In both cases
the autonumber field would be used for defining relations between tables.


As a guess: for reasons of optimization because PRIMARY KEY determines
physical ordering on disk (clustering). See:

Microsoft Jet 3.5 Performance Overview and Optimization Techniques
http://msdn.microsoft.com/archive/de...baseEngine.asp

"From a performance perspective, there are many reasons to frequently
compact a database. One reason is that compacting will create a new
database that stores all table rows in a contiguous order. If a
primary key or unique index is defined, the rows will be sorted in
order of the primary key or unique index. This allows Microsoft Jet to
take full advantage of its read-ahead cache and also reduces disk I/O
when doing sequential scans of a table."

Put the other way around, assigning the table's PRIMARY KEY
designation to the Autonumber column could have a negative impact on
the performance of queries which use a sequential scan on a table e.g.
using the BETWEEN keyword in SQL code.

Jamie.

--



  #30  
Old December 5th, 2007, 11:57 PM posted to microsoft.public.access.tablesdbdesign
Fred
external usenet poster
 
Posts: 1,451
Default Data Primary key vs. Artificial (Autonumber) primary key

I'm from Chicago, we don't know what fault lines are. Could you use a
tornado analogy so that I can understand?

:-)


Fred


"John W. Vinson" wrote:

On Wed, 5 Dec 2007 05:18:01 -0800, Fred
wrote:

And that means at least occasionally correcting or
changing it.


well... there *is* Cascade Updates to cover that possibility.

But I agree, it's ideal if the primary key is rock solid stable. If it's only
San Andreas faultline rock solid stable, you need to depend on cascade
updates, which do work but have their own problems!

John W. Vinson [MVP]

 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 05:01 PM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.