Informix data types

Just wondering if there is a way to reinitialize a column of serial data type. Let's say we have a table with and id column of serial data type. If the current value of thie column is 300, is there a way to make it restart from 1? If not, I know we can create a sequence to simulate the serial column. I tried the following but it gives me error create sequence seq; create table tablename (id integer default seq.nextval)The create table statement generates an error. Can someone tell me what's wrong with this statement. Thanks _________________________________________________________________

2008/10/7 Georges Martin <georges_martin_1@hotmail.com>: > Just wondering if there is a way to reinitialize a column of serial data type. > Let's say we have a table with and id column of serial data type. If the > current value of thie column is 300, is there a way to make it restart from 1? > If not, I know we can create a sequence to simulate the serial column. I tried > the following but it gives me error > create sequence seq; > create table tablename (id integer default seq.nextval)> The create table statement generates an error. Can someone tell me what's > wrong with this statement. > Thanks > _________________________________________________________________ > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > TFM says ALTER TABLE nnn MODIFY ( serial_col SERIAL (x) ) Keith

Hi, ALTER Sentence gives you chance to reinitialize serial column, but always must be with higher values that maximum value for serial that currently is in table. ALTER TABLE A MODIFY field1 serial star(value); Sequences are objects spared from your table. You can ask for next value or execute it directly in your INSERT statement. I.E. create table test( oid int); create sequence test1; insert into test values (test1.nextval); insert into test values (test1.nextval);alter sequence test1 restart 1; Sequences can be reset, but be aware when you reset this values, because they can cause problems if you have inserted rows with this values in your table. Unless you have a unique constraint in column you won't realize that you are duplicating values. Check INFORMIX SQL Syntax for detailed information.

That won't work, Keith. It runs without error but the alter doesn't do anything. You can only alter the current serial value up not down. The way to do it is to alter the serial to 2^31-1 then insert that row and the next insert will wrap back to one: alter table mytable modify serialcolname serial(2147483647); insert into mytable( serialcolname<, other NOT NULL columns>) values (0<,....>); delete from mytable where serialcolname = 2147483647; The next inserted row will have a value of 1 inserted. Note that if there are any existing rows at higher values, inserts of value zero which would insert a duplicated value will get errors IFF there is a unique index/constraint on the serial column (if not there will be a duplicated value) but it will increment the next serial value past the duplicated value. SERIAL will not automatically skip existing values. Unless you have deleted all rows with lower order values that might clash you may want to modify application code to trap the duplicate key errors (there three are different ones depending on whether there is only a unique index or also a unique constraint and/or primary key constraint) and retry the insert in a loop until it is accepted. Art On Tue, Oct 7, 2008 at 1:48 PM, Keith Simmons <smiley73@googlemail.com>wrote: > 2008/10/7 Georges Martin <georges_martin_1@hotmail.com>: > > Just wondering if there is a way to reinitialize a column of serial data > type. > > Let's say we have a table with and id column of serial data type. If the > > current value of thie column is 300, is there a way to make it restart > from > 1? > > If not, I know we can create a sequence to simulate the serial column. I > tried > > the following but it gives me error > > create sequence seq; > > create table tablename (id integer default seq.nextval)> > The create table statement generates an error. Can someone tell me what's > > wrong with this statement. > > Thanks > > _________________________________________________________________ > > > > > > > > ******************************************************************************* > > Forum Note: Use "Reply" to post a response in the discussion forum. > > > > > > TFM says ALTER TABLE nnn MODIFY ( serial_col SERIAL (x) ) > > Keith > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > -- Art S. Kagel Oninit (www.oninit.com) IIUG Board of Directors (art@iiug.org) Disclaimer: Please keep in mind that my own opinions are my own opinions and do not reflect on my employer, Oninit, the IIUG, nor any other organization with which I am associated either explicitly or implicitly. Neither do those opinions reflect those of other individuals affiliated with any entity with which I am affiliated nor those of the entities themselves.

Oh, the problem with your use of the sequence is that a DEFAULT clause has to specify a constant, it cannot execute the nextval method. You could put an insert trigger on the table that calls nextval and replaces the key column if it's inserted with a zero - like a SERIAL behaves. Another option, if you are using IDS 10.00 or later would be to alter the SERIAL column into a SERIAL8 (or if you have IDS 11.50 to a BIGSERIAL which is only 64bits as compared to SERIAL8's 80 bits) which won't wrap until it reaches 9,223,372,036,854,775,807. At one row inserted per second that won't wrap for 133 billion years! Art On Tue, Oct 7, 2008 at 1:33 PM, Georges Martin <georges_martin_1@hotmail.com > wrote: > Just wondering if there is a way to reinitialize a column of serial data > type. > Let's say we have a table with and id column of serial data type. If the > current value of thie column is 300, is there a way to make it restart from > 1? > If not, I know we can create a sequence to simulate the serial column. I > tried > the following but it gives me error > create sequence seq; > create table tablename (id integer default seq.nextval)> The create table statement generates an error. Can someone tell me what's > wrong with this statement. > Thanks > _________________________________________________________________ > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > -- Art S. Kagel Oninit (www.oninit.com) IIUG Board of Directors (art@iiug.org) Disclaimer: Please keep in mind that my own opinions are my own opinions and do not reflect on my employer, Oninit, the IIUG, nor any other organization with which I am associated either explicitly or implicitly. Neither do those opinions reflect those of other individuals affiliated with any entity with which I am affiliated nor those of the entities themselves.