Hi,

I wanted to know if there was a way to re-seed the serial column values. What happens is I have a table with this field and it's data gets repeatedly deleted and inserted. This keeps incrementing the value of this field to one larger than what previously was (even if there is any data or not). There are not more than 50 records in the table and the incrementing of this field's value takes it to a very high end, currently somewhere around 9,00,000 to 50+ of it. Ideally it should always be 1 to 50. Is there a way to control this? I insert 0 for the column.

Hi,

Use the following:

alter table tab_nm modify (serno serial(xxx));

where xxx is the number from where you want the next number to be generated.

Bye

Nitin

Thanks Nitin, I will try that out.

It did not help. The serial column value still starts from the bigger value.. Any other approaches to this?

//although I modified my current logic to use this field also to populate a temp table that was used to copy data from - so that helped solved my case... but if a generic solution can be provided...?

Documentation:
You cannot set the next value below the current maximum value in the column because that action can cause the database server to generate duplicate numbers. You can set the next value, however, to any value higher than the current maximum, which creates a gap in the series of values.

If the new serial value that you specify is less than the current maximum value in the serial column, the maximum value is not altered. If the maximum value is less than what you specify, the next serial number will be what you specify.

NO..no.. actually I even delete all rows and then start populating data.. but still that first row would have an id that is next value greater than what was the maximum of the deleted rows.

This is what I am amazed about.. by the way it is Informix Dynamic Server 9.3. This should not happen, should it?

Thanks I will try that out Too

The INFORMIX database server uses an internal counter
which is
independent from the rows stored in the table.
So this is correct:
INSERT INTO customer VALUES ( 0, 'TECHNOPROS' );
INSERT INTO customer VALUES ( 0, 'SOFTLABS' );
DELETE FROM customer;
INSERT INTO customer VALUES ( 0, 'PENDRAGON' );

SELECT * FROM customer;

custid custname
------------------------------------------------
3 'PENDRAGON'

Hi exterminator,

you could delete your rows and set the next serial value to 1
(or if not empty to max of the remaining rows + 1):
alter table tab_nm modify (serno serial(xxx));

or change the data type of your serial column to integer
and change your ids manually (or by script) with update statements as you want (but with unique values>0),
reset data type to serial
and set the next serial value like above

I dont think that there is an informix command to do all that in one step.

Best regards
ifx

Hi exterminator,

The behaviour you describe is typical for a serial column so if you want a column with consecutive numbers and still have to perform deletes you should solve this with a different approach.

You should make the column an integer one and do the numbering either in your application before the insert or during the insert with a recurring trigger.
For the latter you need to define a delete trigger and an insert trigger on the table. The delete trigger then decreases every number above the deleted one with an update statement. The insert trigger calls a stored function that returns an integer into the number column. Make shure you do the counting consequentially with a COUNT(*)+1 instead of a MAX(number)+1, this way you're certain that you'll never get a gap between numbers (cause if there is the insert will fail).

A serial column is primarily meant for use as an internal key, I think. And if you choose to use it live with the imperfections of it...

Regards

A lot of replies. Great.

A big thanks to all of you who participated.

I agree to the solutions provided by ifx and Tyveleyn. stanislav.ondac, I agree that it might be the expected behaviour as per Informix but having such a field and getting high values for serial columns is just useless.

Changing the datatype is not viable at this point of time. For the sake of solution that I applied was that I was using a temporary table for storing some intermediate values. I added a serial column in that as well and when I used this TEMP table to copy stuff into the relevant table I inserted the column values as generated instead of 0. That solved it.

Needless to say, of course there are multiple solutions. And all that you folks suggested would work just fine. Thanks everyone once again.

Glad to get help. I always get helped here.. its the greatest place for Informix (considering the lack of proper documentation). But if the search on forums could be more efficient - that would simply be fantastic (right now it is so slow).