Re: Serial Numbers Skipping

>In one of my databases, I have a field declared as serial. It was working >fine until it got to 39184. Then the next number that was assigned was >2,038,451. >No records exist between 39,184 and the 2 million number. >I need to keep the same serial number that has been assigned to each >record. >How can I get the serial number back to 39,000?? just rebuild the >database? { alter serial column to integer for a minute } ALTER TABLE table MODIFY (serialcolumn INTEGER NOT NULL);{ decrement any rows that have been inserted with the high number } { by 2038451 - 39185 } UPDATE table SET serialcolumn = serialcolumn - 1999266 WHERE serialcolumn >= 2038451{ update any child tables by the same amount as required } UPDATE jointable SET joincolumn = joincolumn - 1999266 WHERE joincolumn >= 2038451... { alter the column back to serial, which also resets the serial counter to the MAX value currently in the table } ALTER TABLE table MODIFY (serialcolumn SERIAL NOT NULL); Indexes on the column should follow the modifications automatically without any intervention. As to *why* the serial numbers were skipped, I have to believe that a row with serial value 2,038,450 was inserted and then deleted unless it can be proven otherwise. ie: let it break again before worrying about it. ======================================================================= 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.