serial columns (was <No Subject Supplied>)

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>) ). 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. Regarding uniqueness in a SERIAL column, many people assume that there is *always* a unique constraint or unique index on a SERIAL column, automatically. This is not so. The ISQL define-a-table screen will default to that, but if you use the CREATE TABLE statement explicitly, a SERIAL column is just an INTEGER columnthat gets the extra feature of automatic generation of a new value on any INSERT that specifies zero for that column. You want a unique constraint, you have to specify one. For the typical semantic purposes to which a SERIAL column is put, uniqueness is needed and assumed -- but not always, as Mark Herringslake's original query indicated. Hope this clarifies something or other. P.S. Is anybody building a FAQ? This could probably go in it... Dave Cortesi (cortesi@informix.com)