Re: serial columns (was <No Subject Supplied>)
>From: uunet!iest.informix.com!cortesi (David Cortesi)
>Subject: serial columns (was <No Subject Supplied>)
>Date: 20 Apr 93 19:14:36 GMT
>X-Informix-List-Id: <news.3082>
>Asserts the usually-reliable Dave Snyder:
>> The only way I can think of getting a serial filed to
>> start over is to enter 2,147,483,647 records or drop/recreate
>> the table.
>Similarly quoth Dan Guillory,
>> If memory serves me correct, you have to drop the table and recreate it
>> or maybe just the serial field.
>Neither is correct; however I see that the true case is not explicitly
>reflected in the SQL Reference (4.1 level, only one handy). The answer is:
>Just as you CREATE TABLE...(...ser_col_name SERIAL(<start_number>)...),
>you can ALTER TABLE...MODIFY( ser_col_name SERIAL(<new_start_number>) ).
You live and you learn -- I didn't know that!
Unfortunately, changing the start number only works if you increase the
start number. I did:
CREATE TABLE junk01
(
col01 SERIAL(1000) NOT NULL primary key CONSTRAINT pk_junk01,
col02 CHAR(20) NOT NULL
);
INSERT INTO junk01 VALUES(0, "ABCDEFG");
SELECT * FROM junk01;
ALTER TABLE junk01 MODIFY (col01 SERIAL(2000) NOT NULL);
INSERT INTO junk01 VALUES(0, "ABCDEFG");
SELECT * FROM junk01;
ALTER TABLE junk01 MODIFY (col01 SERIAL(3000) NOT NULL);
SELECT tabid FROM Systables;
ALTER TABLE junk01 MODIFY (col01 SERIAL(1000) NOT NULL);
INSERT INTO junk01 VALUES(0, "ABCDEFG");
SELECT * FROM junk01;
And the third row was allocated serial number 3000, so trying to reset the
serial column to a lower value has no effect.
You can re-cycle the numbers by inserting a row with the serial column set
to 2147483646 (2**31 - 2), then insert a row with the serial column set to
0. The next record after that will be assigned serial 1 unless you either
do the ALTER TABLE or insert a row with a specified serial number.
Continuing from the last example...
SELECT * FROM junk01;
INSERT INTO junk01 VALUES(2147483646, "ABCDEFG");
INSERT INTO junk01 VALUES(0, "ABCDEFG");
INSERT INTO junk01 VALUES(0, "ABCDEFG");
SELECT * FROM junk01 ORDER BY col01;
Output:
1 ABCDEFG
1000 ABCDEFG
2000 ABCDEFG
3000 ABCDEFG
2147483646 ABCDEFG
2147483647 ABCDEFG
If you try to insert 2147483647 directly, the serial counter is unchanged,
so when I dropped and rebuilt the table and reran the inserts except for
changing 2147483646 to 2147483647, the output was:
1000 ABCDEFG
2000 ABCDEFG
3000 ABCDEFG
3001 ABCDEFG
3002 ABCDEFG
2147483647 ABCDEFG
>In recent versions (4.1 and after?) I believe this is a "trivial" ALTER
>TABLE which does not reconstruct the table (similar to an ALTER of extent
>sizes).
This is certainly correct in 5.01.UC1A1 -- the tabid does not change.
Yours,
Jonathan Leffler (johnl@obelix.informix.com) #include <disclaimer.h>