Q
Problem solve Get help with specific problems with your technologies, process and projects.

Using generated numbers for primary keys

What are the advantages and disadvantages of using sequentially generated numbers or randomly generated numbers...

for primary database keys?

Was the intent of your question to distinguish the pros and cons of sequential versus random numbers? Sequential numbers never repeat, and you won't run into trouble until the next number is larger than the column can hold, which is usually not a problem unless you define the column as tinyint or something. Random numbers might repeat since they are only really pseudo-random, but I wouldn't worry about it, because, again, you're probably not going to have a table large enough for this to occur. Random numbers also have the advantage that you won't be tempted to try SELECT MAX(ID) in order to get back the key of the latest inserted record -- but that's a different discussion.

It's more likely that you're interested in the advantages and disadvantages of a surrogate key as compared to a natural or "real" primary key.

Key Advantages Disadvantages
natural
  • self-identifying
  • bulky
  • may repeat
  • may be null
  • may change
surrogate
  • lightweight
  • unique
  • not null
  • never changes
  • meaningless

As you may be able to tell from the above, the advantages of one are the disadvantages of the other.

This answer is continued.


This was last published in June 2002

Dig Deeper on Oracle and SQL

PRO+

Content

Find more PRO+ content and other member only offers, here.

Have a question for an expert?

Please add a title for your question

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.

Start the conversation

Send me notifications when other members comment.

By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Please create a username to comment.

-ADS BY GOOGLE

SearchDataManagement

SearchBusinessAnalytics

SearchSAP

SearchSQLServer

TheServerSide

SearchDataCenter

SearchContentManagement

SearchFinancialApplications

Close