Re: Reset values in serial datatype
>From: dbaresrc@xmission.xmission.com (DBA Resources) >Date: 23 Feb 1995 05:51:06 GMT >X-Informix-List-Id: <news.11737> > >I thought I had read in here a discussion about resetting the starting >values of a serial datatyped column. I just got finished looking through >the indexes of this newsgroup at mathcs.emory.edu and found nothing. >[...] >Is there a way of resetting the value of a serial datatype back to 0?? I thought this should be in the FAQ, but it isn't. Kerry: please would you consider rectifying this omission. Thanks. I know I've sent this info to c.d.i before, but I can always send it again as required... I've slightly revised the notes. Jonathan Leffler (johnl@informix.com) #include <disclaimer.h> --------------------------------------------------------------------------- Date: Fri, 13 Sep 91 11:13:54 GMT From: johnl@javalin (Jonathan Leffler) Subject: Re: Reset a serial column ************************************************************** ** TO DEMONSTRATE THE BEHAVIOUR OF SERIALS AND RESEQUENCING ** ************************************************************** -- Explanation: the lines starting with a plus are SQL commands. -- Other lines are the output from the preceding SELECT statement -- in a pipe-delimited format. + create database junk + create table junk (col00 serial(1000) not null, col01 char(10) not null) + create unique index pk_junk on junk(col00) + insert into junk values (0, "A" ) + select max(col00), min(col00) from junk 1000|1000 + select * from junk 1000|A + insert into junk values (2147483647, "a" ) + select max(col00), min(col00) from junk 2147483647|1000 + select * from junk 1000|A 2147483647|a + insert into junk values (0, "A" ) + select max(col00), min(col00) from junk 2147483647|1000 + select * from junk 1000|A 2147483647|a 1001|A + drop table junk + create table junk (col00 serial(1000) not null, col01 char(10) not null) + create unique index pk_junk on junk(col00) + insert into junk values (0, "A" ) + select max(col00), min(col00) from junk 1000|1000 + select * from junk 1000|A + insert into junk values (2147483646, "a" ) + select max(col00), min(col00) from junk 2147483646|1000 + select * from junk 1000|A 2147483646|a + insert into junk values (0, "A" ) + select max(col00), min(col00) from junk 2147483647|1000 + select * from junk 1000|A 2147483646|a 2147483647|A + insert into junk values (0, "A" ) + select max(col00), min(col00) from junk 2147483647|1 + select * from junk 1000|A 2147483646|a 2147483647|A 1|A + insert into junk values (0, "A" ) + select max(col00), min(col00) from junk 2147483647|1 + select * from junk 1000|A 2147483646|a 2147483647|A 1|A 2|A + close database + drop database junk -- It may not make all that much sense, but that's what happens. -- FYI: the engine was Standard Engine Version 4.00.UD2, but the -- behaviour is shown by all known versions (1.10 through 7.10). -- Additional notes: -- When the numbers are recycled, the engine does not know about -- handling conflicts. So if there was a row with serial value 2 -- already in the database, then the last insert statement above -- would fail with a -239 error (assuming that there is a unique -- index on the serial column).