Re: Serial Field
kurt.vanwagoner@sltrib.com (Kurt Vanwagoner) writes:
>Is there a way to reset a serial field to zero, without dropping the
>table. Assuming there is no data in the table. I am using Informix
>online 5 on a NCR unix box.
OK, I won't ask why you can't just drop the table.
The next value for a SERIAL column is stored is an unaccessable area of
the tablespace entry for the table, so I doubt if there's any direct
way of resetting it to 0. BUT ... enjoying such challenges ("can you
drink this water without touching the glass?"), I came up with:
CREATE TABLE srl (srl SERIAL);
INSERT INTO srl VALUES(0);
INSERT INTO srl VALUES(0);
INSERT INTO srl VALUES(0);
INSERT INTO srl VALUES(0);
INSERT INTO srl VALUES(0);
INSERT INTO srl VALUES(0);
INSERT INTO srl VALUES(0);
INSERT INTO srl VALUES(0);
INSERT INTO srl VALUES(0);SELECT "BEFORE ", srl.* FROM srl; {* values 1-9 are shown *}
DELETE FROM srl WHERE 1=1;
ALTER TABLE srl MODIFY (srl INTEGER);
ALTER TABLE srl MODIFY (srl SERIAL);
INSERT INTO srl VALUES(0);SELECT "AFTER ", srl.* FROM srl; {* value 1 is shown *}
DROP TABLE srl;
I tried just modifying srl back to SERIAL (*without* the INTEGER
intermediate step) without success. You have to fool the engine into
thinking it won't need to track the next highest values.
BTW: this also works if the table isn't empty; it sets the next SERIAL
value to the value one higher than the current highest value.
=======================================================================
Dennis J. Pimple dennisp@informix.com Opinions expressed
Senior Consultant -------------------- are mine, and do not
Informix Software Inc Voice: 303-850-0210 necessarily reflect
Denver Colorado USA Fax: 303-779-4025 those of my employer.
Call me ... "Kludgemaster!"