On Sun, 2006-09-24 at 14:49 -0700, Bob Pawley wrote:
> It's the behavior I expect - but the gaps aren't acceptable.
>
> Bob
Then using the SERIAL or SEQUENCE won't do you any good.
A possible solution for this would be to regenerate the entire column's
values every time a record gets deleted starting form 1. but then again
this would be very slow if you have a very large table
Another solution would be to have two triggers, one for delete and one
for insert plus an extra *reserve* table to reserve the deleted value.
The delete trigger would save the *deleted* values in the reserve table
and when a new record is inserted the insert trigger first would check
the reserve table for deleted values (that are stored by the delete
trigger) if a value exist then it would use that value or increment that
last value.
However if you want to use a *no gap* sequence as a primary key, you
should be aware that you will destroy the integrity of you data.
--
Regards,
Gevik Babakhani