homepage Welcome to WebmasterWorld Guest from 54.226.235.222
register, free tools, login, search, pro membership, help, library, announcements, recent posts, open posts,
Pubcon Platinum Sponsor 2014
Home / Forums Index / Code, Content, and Presentation / Databases
Forum Library, Charter, Moderators: physics

Databases Forum

    
Question about using auto Incrementing Primary keys as a record IDs.
nelsonm




msg:4355978
 12:17 am on Aug 28, 2011 (gmt 0)

Hi all,

It was brought to my attention by rocknbil that using the auto incrementing primary key or any auto incrementing key for a record ID such as a customerID or employeeID is not a good idea.

The issue is related to problems that can occur if the table ever has to be regenerated after records have been deleted, the table is damaged or unrecoverable.

If the table; is damaged, not recoverable, had records deleted or the table was exported without adding the "AUTO_INCREMENT" value of auto increment fields, there will be no way to reconnect existing foreign ID keys to there original primary ID keys when the table is regenerated. You'll end up having existing foreign ID keys pointing to the wrong records in other tables.

The only table i currently have that does not use the auto increment primary key for the record ID is my State table. While primary key is used to insure unique records, the unique state FIPS code as the primary record ID i refer to in other tables as foreign keys.

So is the solution to...

1. Continue to use auto incrementing primary keys as record IDs, just make sure you have a rotating weekly nightly backups of the production databases with auto increment values included?

2. Continue to use auto incrementing primary keys to insure unique records, but create some sort of artificial none auto incrementing natural key for your record IDs? Still make sure you have rotating nightly backups of the production databases, but this time the auto incrementing keys are rendered unimportant for anything other than making sure that records are unique. Having the auto incrementing keys regenerated has no affect of the relationships between table foreign and primary ID keys.

What do you all think?

For those who prefer to create artificial record ID keys, what would you use to generate them for such things as customer or employee table IDs?

 

jbroder




msg:4362137
 9:40 pm on Sep 13, 2011 (gmt 0)

I wouldn't consider myself an authority, and if an authority had answered you I would bow out. But your thread looked lonely...:)

I was interested in rocknbill's reply to your question as well. I just re-read it and I think he is not advocating never using autoincrement. I think he was just point out that for states, where you might have a reasonable expectation that "you know what #3 is, it is always alaska" it might be better to make your own id numbers, and not rely on autoincrement.


In all these things there are trade offs, easy vs well-engineered. How easy do you need it to be vs how much time do you have to make it well-engineered?

Hope that is useful...

nelsonm




msg:4362417
 3:50 pm on Sep 14, 2011 (gmt 0)

thanks for you response. I have made my bed (decision about auto inc.) and i'm sleeping in it. I just hope the bed bugs don't come out and bite me in the a--!

Global Options:
 top home search open messages active posts  
 

Home / Forums Index / Code, Content, and Presentation / Databases
rss feed

All trademarks and copyrights held by respective owners. Member comments are owned by the poster.
Home ¦ Free Tools ¦ Terms of Service ¦ Privacy Policy ¦ Report Problem ¦ About ¦ Library ¦ Newsletter
WebmasterWorld is a Developer Shed Community owned by Jim Boykin.
© Webmaster World 1996-2014 all rights reserved