When to / not to use auto identity incrementing IDs

Question

When to / not to use auto identity incrementing IDs

Grant_S

SSC Veteran

Points: 225
More actions
August 26, 2004 at 4:50 pm

#164727

Hi
One hears negative comments from some people about the use of auto identity incrementing IDs. I have designed a few small databases - and also tables within larger databases - and have generally used these without any problem.
I have witnessed issues when MS Access (97 or 2000) were corrupted and know that people were cursing auto incrementing Keys. I have not had to deal with this myself in Access or SQL.
What is the best practice regarding this matter?
What are the pros and cons of using or not using auto-incrementing identies?
Is space unecessarily wasted where rows have been deleted and "gaps" now exist ?
How is response / database efficiency affected?
Thanks in advance.
Cheers
Grant

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply

DCPeterson SSCoach Points: 15266 More actions · Answer 1

You might have just reignited one of the oldest debates in database design. There are those who claim that natural keys are "fragile" and should always be avoided. Then there are those who claim that surrogate keys are evil. Neither of these camps is right.

What's the difference between a "natural key" and a "surrogate key"? The answer is really nothing more than when the key is assigned. A surrogate key is merely a key that didn't exist prior to the system that creates it.

I think there are good reasons to use both. The two biggest benefits of surrogate keys is that they allow the use of simple keys as opposed to composite keys in many cases. Where this really becomes beneficial is when you have to migrate the key of one table into other tables for referential integrity. The other benefit is when the natural key needs to be updateable, surrogate keys allow this without breaking RI, however the advent of cascading RI has largely eliminated that reason.

Some of the drawbacks to surrogate keys are that you are introducing additional data to store and manage, the surrogate key generally has no meaning and should generally not be exposed to users. The biggest problem I see with surrogate keys has nothing to do with surrogate keys per se, but rather with the ignorance of those who sometimes use them. Surrogate keys are a physical, rather than a logical concept and tend to impart a false sense of uniqueness to those who don't know any better. In other words surrogate keys cannot be used to ensure logical correctness (no duplicates) and should not be considered in the normalization process. Regardless whether you choose to use surrogate keys you should identify the natural key and make sure it is enforced.

As far as creating "gaps" this isn't an issue because the key values do not determine how the data is physically stored on disk. If you, for some reason can't have gaps in the number sequence of your key there are workarounds for that, but generally when I have seen that as a requirement, it is due to a misunderstanding of the real requirement and of database design in general.

/*****************

If most people are not willing to see the difficulty, this is mainly because, consciously or unconsciously, they assume that it will be they who will settle these questions for the others, and because they are convinced of their own capacity to do this. -Friedrich August von Hayek

*****************/

Grant_S SSC Veteran Points: 225 More actions · Answer 2

Thanks for your reply and all the info therein.

Armed with the correct terms you provided "Surrogate" and "Natural", I searched Google and ..... Yup! I sure can see the debate you talked about. Lots to read and think about.

Grant