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. |
|
|
Thread Tools | Display Modes |
#21
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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 | |
|
|