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