--- In firebird-support@yahoogroups.com, Adriano <fadrianoc@...> wrote:

>
> Continuing my previous post, i'm making some test and see that seems
> that when delete a record, firebird 'mantains' memory of the last
> inserted Primary Key and if
> BEFORE DELETE i have
> 1
> 2
> 3
> 4
>
> AND then delete record with ID 2
> AFTER DELETE, when insert a new record automagically firebird
> assign
> ID number 5
> 1
> 3
> 4
> 5
>
>
> In my case FB database created on old access database seems not have
> memory (obviously) of the old Primary Key and so i receive the error
> described when try to insert a new record.
>
> Is it correct ?
> What should be a solution for that ?
>
> Thanks
> Adriano

Why do you want this?

Primary keys should have no importance to anything other than the
database itself. It does not matter if there are gaps where records
have been deleted or an insert was rolled back, if you use a bigint
datatype for your primary keys, and insert 100000 records per second,
then you have nearly million years before you will overflow.

What you are describing is slow and unnecessary and will cause you
errors once you expand to more than a single user running a single
transaction. You need to do some reading about transaction isolation
and you will see why your solution will not work in such an environment.

If you wan't to insert a record, call the generator with an increment
of 1.

eg,

select gen_id(my_generator, 1) from RDB$DATABASE

If you don't end up using it, it does not matter. If you delete it
later, it does not matter. Once a number is used, it is gone, forgotten.

Adam