Re: serial columns (was <No Subject Supplied>)

In article 20182@informix.com, I said: >Just as you CREATE TABLE...(...ser_col_name SERIAL(<start_number>)...), >you can ALTER TABLE...MODIFY( ser_col_name SERIAL(<new_start_number>) ). > >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). > >Clearly if you alter the starting point to a smaller value than the last >inserted number, you can create duplicate values. Jon Leffler actually did the test and made me eat the last paragraph. You *can* reset the start number using the MODIFY clause, and it is a trivial alteration (doesn't copy the table) as I said. However, if you specify a new starting value that is less than the current (next) serial number, it is ignored. Hence you cannot use ALTER TABLE MODIFY to set a serial column back to an earlier value. This behavior has been sent to the Informix "doc" alias and will presumably appear explicitly in some forthcoming edition of the SQL Reference. (It's there implicitly now, if you follow the MODIFY clause railroad diagram out very carefully...) Dave Cortesi (cortesi@informix.com) P.S. Any Real(tm) Programmer immediately wonders, hmmm, what would happen if I used ALTER TABLE to set a serial column to 2**31? If you want to make the experiment, I recommend you start by setting it to 2**31-2 (2147483646) and then inserting three rows. Note: *n*o*w*h*e*r*e* is it documented that a SERIAL column always and forever will be a 32-bit binary integer, so this behaviour is not something you would want to base an application on...