Hi,



I am creating an access database that will be the back end for my visual 

basic program.  I have ready articles that say it is good to define 

meaningless data for your primary key, ie. autonumber, because the data 

might change and it allows for more flexibility.  Also it's faster to have 

a number as the key for querying.



My question is:  Is it good to have an autonumber as your primary key for 

performance and flexibility for future changes?  Any thoughts on this 

would really help me out.



Thanks,



Jared

Jared,



The autonumber field is an easy way to ensure that each record is unique,

however, by itself, it will not prevent records, identical in all respects,

except for the autonumber field to be created. This is not, in my opinion, a

good thing. I prefer to use an autonumber field as a tie-breaker, i.e., to

distinguish two or more instances of say, for example, Jone, John A and

Jones, John B.



Hope this helped.



Walt

Jared:



Certainly in a relational database system like Access a primary key system 

should be defined. Perhaps the best thing to do first is to normalize your 

data at least to the Third Normal Form (3NF). Normalization helps 

eliminate the redundancy of data in a database by ensuring that all fields 

in a table are atomic, and databases in the 3NF are characterized by a 

group of tables storing related data that is joined together through keys 

(primary and foreign). This means that: i) each value in a table is 

represented once and only once; ii) each row in a table is uniquely 

identifiable (that is, has a primary key); and iii) any non-key 

information that relies upon another key should not be stored in the same 

table. Furthermore, any calculated values (such as age) should not be 

contained in a table but determined on an as-needed reporting basis (e.g., 

current date function minus the birthdate field).



Having said all that, 3NF databases provide the best compromise between 

performance, extensibility, and data integrity. In choosing a primary key, 

look at your data during normalization. Is there a unique identifier that 

exists already (e.g., Customer Account Number)? Using it will allow you to 

understand and relate to your database better. Otherwise, use an auto-

increment method, especially where no unique identifier exists (e.g., 

record number).



The one major problem that I have seen with auto-increment primary keys is 

in situations where you want to import data from another database that has 

related table information and the same auto-incremented numbers exist in 

the second database as primary (and foreign) keys also.



An alternative to auto-incremented primary keys in Access is to make two 

fields in a table the primary keys, if that is more suitable to the data 

that you are collecting.



Determining the best way to assign primary keys does come with experience, 

as well as fully understanding the data that you are collecting and the 

nature of your database.



Regards,

Eric

This is an oft-debated topic, and you will find decent arguments on both

sides.  Access gives the natural PK camp a little extra ammo, because of its

'cascade updates to key fields' option.



FWIW, I'm lately of the opinion that the best practice is to use an

artificial PK (e.g., an autonumber), combined with a unique index on the

natural key.



Cheers,



-Roy



Roy Pardee

Programmer/Analyst





-----Original Message-----

From: jared.j.pomranky@a...

[mailto:jared.j.pomranky@a...]

Sent: Friday, September 21, 2001 10:44 AM

To: Access

Subject: [access] Selecting type of primary key for performance and data

integrity





Hi,



I am creating an access database that will be the back end for my visual 

basic program.  I have ready articles that say it is good to define 

meaningless data for your primary key, ie. autonumber, because the data 

might change and it allows for more flexibility.  Also it's faster to have 

a number as the key for querying.



My question is:  Is it good to have an autonumber as your primary key for 

performance and flexibility for future changes?  Any thoughts on this 

would really help me out.



Thanks,



Jared

Thank you everyone for replying.  I have a some more information to think 

about.  I have been on a project where it was all artificial keys and it 

made sense for data that could possibly change but I am looking to see if 

this was correct.  I'm sure that either way has it's pros and cons so if 

anyone else has some comments I would really appreciate them.



Thank you,

Jared



> This is an oft-debated topic, and you will find decent arguments on both

> sides.  Access gives the natural PK camp a little extra ammo, because of 

its

> 'cascade updates to key fields' option.

> 

> FWIW, I'm lately of the opinion that the best practice is to use an

> artificial PK (e.g., an autonumber), combined with a unique index on the

> natural key.

> 

> Cheers,

> 

> -Roy

> 

> Roy Pardee

> Programmer/Analyst

> 

> 

> -----Original Message-----

> From: jared.j.pomranky@a...

> [mailto:jared.j.pomranky@a...]

> Sent: Friday, September 21, 2001 10:44 AM

> To: Access

> Subject: [access] Selecting type of primary key for performance and data

> integrity

> 

> 

> Hi,

> 

> I am creating an access database that will be the back end for my visual 

> basic program.  I have ready articles that say it is good to define 

> meaningless data for your primary key, ie. autonumber, because the data 

> might change and it allows for more flexibility.  Also it's faster to 

have 

> a number as the key for querying.

> 

> My question is:  Is it good to have an autonumber as your primary key 

for 

> performance and flexibility for future changes?  Any thoughts on this 

> would really help me out.

> 

> Thanks,

> 

> Jared

In case anybody else wanted some more information on the subject I thought 

that I would share what I found on a past post and the Wrox FAQ.  It looks 

like we have some of the same people answering the question for me again.  



Why can't I update two tables that I have linked?



D22 Have you used a 'real' data field to link the tables, and are trying 

to change this value? If so, you will be unable to update the parent table 

because there are fields in the child table that depend on the value in 

the parent table. Likewise, if you try to update the child table you get 

an error because there is no matching value in the parent table.Using real 

data as a primary key, otherwise known as a semantic key, is not a good 

idea. Always use an Autonumber field as the primary key, and make sure to 

use this when selecting and linking.



-p2p.wrox.com  FAQ  the programmer?s resource center





Semantic Key versus Real Key

http://p2p.wrox.com/archive/access/2001-02/38.asp



Thanks,



Jared