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

Gaps in autonumber sequences

If I have a database with a table that has a primary key, and let's say it currently has 22 records (each with a primary key that is incremented by one, i.e. 1-22), but at one time there were 23 records. Ordinarily the next time a record is added the next primary key will be 24 since there was once a 23. Is there a way to force this to start again at 23 rather than 24? I don't think just making an identity seed of 23 will work, correct?

There are ways to manipulate the values assigned by an autonumber or identity or auto_increment or sequence. The method varies from database to database, but there is one thing every method has in common: don't do it!!

An autonumber or identity or auto_increment or sequence column has an important characteristic: used as a surrogate key, it has no intrinsic meaning. No part of your application should care what the value is, and most importantly, you should never hardcode the value. It would be bad practice to write a query such as:

select count(*) as WidgetOrders
  from orders
 where orders.productid = 23 -- widgets

Your application should never even let the users see the surrogate key values, even though it may not always be possible to keep them totally hidden. For example, consider this form field:

Select Product:

This form field is created by this HTML:

<select name="productid">
<option value="21">doodad</option>
<option value="22">gadget</option>
<option value="24">gizmo</option> 

This HTML is dynamically generated from the results of this query:

select id
     , descr
  from products
order by descr

Note that Web page users don't actually see the id values unless they know how to view and read HTML. Might they not see the gap? Yes, and someone who knows how to spoof an HTML form might try to submit a productid value of 23, but your server side logic should immediately reject it, by simply looking it up in the products table to ensure it's an existing key value.

The situation is even worse when you consider deletions. If you have sequential numbers from 1 through 937, and then delete number 21, how many rows do you have to update in order to "remove" the gap? How long will that take? And what if there's another table with a relationship to the surrogate primary key? All the foreign key values in the other table will have to be renumbered as well. Yikes!

There is nothing to be gained by renumbering surrogate keys. Leave them alone, and your application will continue to work nicely. There's no benefit to having a gap-free sequence, but trying to maintain a gap-free sequence takes work and exposes you to data integrity problems. If you think you have a good reason, please write to me, because there's usually a better way to achieve whatever you think you need a gap-free sequence for.

Dig Deeper on Oracle and SQL

Start the conversation

Send me notifications when other members comment.

Please create a username to comment.