Re: SERIAL data type

Ooooops, meant to post this to the list in case I made any mistakes or mis-statements. ...don >> Does anyone if their is a mechanism or workaround to get the SERIAL >> datatype to wrap back to 1 (or some other value) when it reaches its >> maximum (2147483647) >> >> I'm working with Informix 5.0 and will be moving to 6.0 and 7.0 >> >> Thanks >> >>--------------------------------------------------------- >> Enda Finnan <clipped> >Serial values will automatically wrap around to an unused value, presumably >1 when they exceed the max. There is no easy way to insert specific values >into serial columns. (There is some way to unload and load values, I think, >but I have forgotten what that is.) > Clem Akins <clipped> I was under the impression that you could indeed insert specific values into a serial column. Assuming table1 with a serial column and any other column, then you would write: INSERT INTO table1 VALUES (0,"Mary");which would insert the next available number. If you write: INSERT INTO table1 VALUES (2000,"John");then you would get 2000 in the serial column. (I assume a row with 2000 did not exist before the insert and there is a unique index on the serial column. If a "2000" already exists, you'd get a -239.) Assuming you have unused numbers in your table--you don't have 2 billion rows, right?--you could use unload/load to "re-serialize" the numbers. I think Clem was referring to this when he talked about the unload/load. The problem with the following approach is if you have references to the serial column in other tables. Then you have to modify their FKs to point to the row with the new (serial) value. Did that make sense? To paraphrase an earlier procedure described by Graeme Sargent: --credit where credit is due-- 0) Assuming your create table was CREATE TABLE table1 (col1 SERIAL NOT NULL, col2 CHAR(10)); 1) UNLOAD TO tabname.unl SELECT 0, col2 FROM tabname; 2) DELETE FROM tabname; 3) ALTER TABLE table1 DROP col1; 4) ALTER TABLE table1 ADD (col1 serial(1) NOT NULL BEFORE col2 ); 5) LOAD FROM tabname.unl INSERT INTO tabname; 6) Re-add any indexes, constraints, default values here or in step 4. Notes: in step 1, name all of your columns explicitly and replace the serial column with a literal 0. Steps 3 and 4 cannot be combined into an alter. If you ALTER/MODIFY the serial column, it will start numbering where you left off, not at 1. Step 4 puts the column back where it originally was "BEFORE col2." If you don't do this, then you'll have to rewrite the INSERT in step5 to use column names to get everything back in its original column. Unless the column was already at the end of the table. There are always so many exceptions to any statement. :-) Since I'm running long anyways: I hope I'm getting this next part right. I've seen the behavior but don't know how to describe it. There is a oddity associated with serial columns (in all releases from 5 on) dealing with how Informix behaves when you insert 2147483647 into a serial column. It continues from the last number inserted before the 2147483647. I can't find it now, but I can look it up and post it if you want it. I believe it was described in detail by Jonathon Leffler. It might be in the FAQ also. Sorry if I over-explained. I tend to do that. Don Boothby (DBA) State of Indiana - ISD Room N551, IGCN 100 N Senate Ave Indianapolis, IN 46204