Re: autoincremental field ?? Bug
"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 |///// / //|
| http://www.iiug.org +-----------------------------------+//// / ///|
| |This email will self-destruct in |/// / ////|
| |10 sec. If you received this email |// / /////|
| |in error, sorry about the mess. |/ ////////|
+----------------------+-----------------------------------+-----------+
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. |/ ////////|
> +----------------------+-----------------------------------+-----------+