EXPERT RESPONSE
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:
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>
</select>
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.
|
