Re: Resetting the value in a serial column
>From: uunet!ccd.harris.com!kristen.m.altman (Kristen Altman) >Message-Id: <9204012004.AA23739@pd2> >Subject: Resetting the value in a serial column >Date: Wed, 1 Apr 92 15:03:59 EST >X-Informix-List-Id: <list.1022> > >Is there a way to reset the serial count without removing everything from the >table, dropping the serial column, and then re-defining the serial column? > >******************************************************* >* Kristen Altman * >* kma@ccd.harris.com * >* Harris Controls and Composition Division * >* Melbourne, FL * >******************************************************* Yes. Here is some mail which discusses what to do. Yours, Jonathan Leffler (johnl@obelix.informix.com) ============================================================================ Date: Mon, 13 May 91 13:41:39 CDT From: dfarrell@infmxchi (Dan Farrell) Subject: Re: Reset a serial column >> Is it possible to reset a serial column to a number less >> than the current value I can't Alter table or copy it >> because of disk space limitations. The insert does not >> reset a value if the inserted value is less than the >> current serial column value stored in the partition header info. > No. This can't be done. However, what you may want to do is insert into > the table the highest serial value (2147483647). The next value that is > inserted will wrap and use the available slots in the table. Well actually you must insert one value lower, 2147483646 to begin resequencing. To say the available slots will be used may be misleading, (some persons may construe the engine intelligently skips over serial values that exist). The engine will choke the first time it tries to insert a serial value that already exists, error -239. Date: Mon, 13 May 91 13:06:15 PDT From: abed@cougar (Abe Danziger) To: brad@tiger, dfarrell@infmxchi, se@tiger, tech@tiger, tmurphy@tiger Subject: Re: Reset a serial column >>> Is it possible to reset a serial column to a number less... > Well actually you must insert one value lower, 2147483646 to begin... Well thats not exactly true. You can have a value 2147483647 inserted and it will resequence after that value. The -239 error will be generated due to the unique index on the serial (serial != unique, unless you created a unique index or created the serial with the isql menu) this is not a cause of the serial. What you can do is loop around until there is no error message and then continue. Not the most elegant... but it works. Abe Date: Mon, 13 May 91 14:10:02 PDT From: hsong@sea (Haiyan Song) To: abed@cougar, brad@tiger, dfarrell@infmxchi, se@tiger, tech@tiger, Subject: Re: Reset a serial column To what I have obeserved from the code and test, inserting a row with the serial column's value assigned to be 2147483646 will make the serial column start resequencing if in the next insert statement that column is set to default(0), it will take the values of (2147483647, 1, 2,3,...) in order. If there is a unique index created on that column, then it will generate an error if duplicates ocurr. Directly inserting a row with (2147483647) in the serial column will not make the column start resequencing if there are already other values in the table, it will actually start from the same value as if the (2147483637) row is not inserted. The reason for this is isuniqueid() is not called if the user provide 2147483637 (non-zero) value for the serial, thus the serial value in the partition header is not changed to (-2147483648), the next call to issetunique(isfd, 1) will not be able to make the resequencing start. Users can insert rows with the serial values to be any thing that does not exist (if there is a unique index on the column), but those inserts will have no effect on the next available value if it is less than the current value (of course if one chooses to make it restart, it is a different story). Haiyan Date: Fri, 13 Sep 91 11:13:54 GMT From: johnl@javalin (Jonathan Leffler) To: johnl@javalin Subject: Re: Reset a serial column ************************************************************** ** TO DEMONSTRATE THE BEHAVIOUR OF SERIALS AND RESEQUENCING ** ************************************************************** + 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. For info: the engine was Standard Engine Version 4.00.UD2 Jonathan Leffler (johnl@asterix) Date: Mon, 13 Jan 92 09:36:19 EST From: larrys@ifmxatl (Larry Stoumen ) Message-Id: <9201131436.AA13088@ifmxatl.> Subject: Re: serial columns Thanks, Jonathan. Here is another reply which also explains it ... >From: junet@cheetah (June Tong) >Subject: Re: Reset a serial column (fwd) >Date: Fri, 10 Jan 92 14:34:27 PST > >================ Forwarded Message From Haiyan Song ===================== >> Subject: Re: Reset a serial column >> >> To what I have obeserved from the code and test, inserting a row with >> the serial column's value assigned to be 2147483646 will make the serial >> column start resequecing if in the next insert statement that columns is >> set to default(0), it will take the values of (2147483647, 1, 2,3,...) in order.If there is a unique index created on that column, then it will generate >> error if duplicates ocurrs. >> >> Directly inserting a row with (2147483647) i