I have a table with the field serial number as the primary key:
create table test (id serial primary key, name char(10))
There is procedures to insert into test(name) values('xx').
And meantime there is purge on test table with certain condition.
Now the id is retrieve to maximum value , so seems the next serial number will restart from 1, So it tries to insert with 1, with dupliated constraints error, then it goes next to 2, again error, repeat insert, till it reaches the first free serial number. Seems it is automatically find the gap for the free seiral numbers which are purged.
Could anybody tell me how to solve this problem?
Is there any configuration should be done on informix, or on the table,
in order to get the free serial number automatically?