RE: autoincremental field ?? Bug

Just tried Marks suggestion on 7.24.UC8/Solaris 2.6 - works a treat Paul Watson # WF Software Ltd # You are only young once Tel: +44 1436 674729 # but you can be immature Fax: +44 1436 678693 # forever www.wfsoftware.com # > -----Original Message----- > From: Ioannis Demetriades [mailto:ioannis@ctl.com.cy] > Sent: Thursday, June 22, 2000 6:50 AM > To: informix-list@iiug.org > Subject: Re: autoincremental field ?? Bug > > > Mark, > > By inserting a record using INSERT INTO office VALUES ( 0, NULL , NULL > .... ) you will not be able to re-use a serial number that > has already been > used, even though the record with the next available serial > number is not in > your database. > > You could reuse that serial number only if you did INSERT INTO office > VALUES(656,NULL,....) > > > Mark D. Stock <mdstock@mydas.freeserve.co.uk> wrote in message > news:8irett$k1c$1@news.xmission.com... > > > > "COOPER, Joseph" wrote: > > > > > > > >Is there a way to make a field of type INTEGER > > > > autoincremental (like an > > > > >index)? > > > > > > > > SERIAL? You know, I'm in no position to talk, but I think you > > > > need to RTFM. > > > > > > :-)) > > > Ok, well I HAVE been reading the manual and I have a > related problem. > > > I have a table with a serial column that has about 400 rows. > > > > > > The table structure is along the lines of > > > > > > CERATE TABLE office > > > ( > > > off_code SERIAL, > > > some other fields ( descriptions and > address only ) > > > ); > > > > > > The table has an index on off_code > > > > > > SELECT MAX ( off_code ) FROM office; -- returns 636 > > > > > > INSERT INTO office VALUES ( 0, NULL , NULL .... )> > > > > > SELECT MAX ( off_code ) FROM office; -- returns 654. > > > I can understand how this happens but to reset it I > thought you have to > run > > > > > > DELETE FROM office WHERE off_code > 636; > > > ALTER TABLE office MODIFY off_code SERIAL(637);> > > > > > I have tried this so many times that now I'm at about 700. > > > > > > Is there something that I'm missing or is this a bug? > > > > > > Informix version IDS 7.30UC3. > > > OS AIX 4.2 > > > > > > Any help greatly appreciated > > > > AFAIK the only way to change the SERIAL next number is with CREATE > > TABLE. :-) > > > > However, have you tried: > > > > ALTER TABLE office MODIFY off_code INT; > > ALTER TABLE office MODIFY off_code SERIAL(637);> > > > Cheers, > > -- > > Mark. > > > > > +----------------------------------------------------------+-- > ---------+ > > | Mark D. Stock mailto:mdstock@mydas.freeserve.co.uk > |//////// /| > > | http://www.informix.com http://www.informixhandbook.com |///// / > file://| > > | http://www.iiug.org > +-----------------------------------+//// / ///| > > | |This email will self-destruct in > |/// / ////| > > | |10 sec. If you received this email > |// / /////| > > | |in error, sorry about the mess. > |/ ////////| > > > +----------------------+-----------------------------------+-- > ---------+ > > The contents of this e-mail are confidential to the ordinary user(s) of the mail address(es) to which it was sent and may be legally privileged. If you are not the intended recipient, any disclosure, copying, distribution or use of it, or any part of it, in any form whatsoever, and any actions taken or omitted to be taken in reliance on it, is prohibited and may be unlawful.