Resetting the value for the serial data type

[testhandle]

Hi,

Informix online 5.0 serial data type has a limitation of max value 2,147,483,647. Is there any way to reset the value to 1 after reaching the max?

Thanks
John Jayaseelan

 

[PHV]

Take a look at the ALTER TABLE ... MODIFY clause.

Hope This Help, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884

 

[testhandle]

Is there any other way to implement this (For example in the create table syntax itself).

 

[PHV]

In the create table, use SERIAL, with n as the starting number.

Hope This Help, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884

 

[testhandle]

The table is created using 'SERIAL', how the value of serial will be reset after reaching the max. Does that happen or needs to be reset.

 

[vpshriyan]

Hi John Jayaseelan,

>Does that happen or needs to be reset

Well, it does automatically, no manual intervention is needed. Here is the evidence:

create temp table xtable (xcol serial(50)) with no log;
insert into xtable values (0);
insert into xtable values (0);
select * from xtable;
insert into xtable values (2147483647);
insert into xtable values (0);
insert into xtable values (0);
select * from xtable;

However, if the serial field has unique/primary constraint, the insert will fail.

Regards,
Shriyan