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
  • self-identifying
  • bulky
  • may repeat
  • may be null
  • may change
  • 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.

Dig Deeper on Oracle and SQL

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.

Please create a username to comment.