Re: Serial Fields

Scott Leckie (scott@ascsys.demon.co.uk) wrote: : How constant are SERIAL fields? : We have tables where SERIAL's are used as the primary index and other : tables are referenced via this value. : We also have a front-end schema editor which unloads and reloads all : of the tables whenever the layout changes. : So far the links have been retained because ISQL seems quite happy : to accept the consecutive numbers poked back into the SERIAL field from : the unload data. : Will this always be the case? : I'm starting to quake now that at sometime in the distant future : ISQL will decide to reorder the SERIALs at load time leaving the : links as gibberish. When a value is inserted into a serial column, Informix performs two checks: * If the column is zero, it is replaced with the next serial value and the next serial value is incremented. * If the column is greater than the next serial value, the value is reset to (column + 1), so future serial inserts will get a unique value. Other than that, a serial column behaves exactly like an integer during insert. Consequently a simple unload/load of a table with a serial column should always preserve integrity of the data. There are pitfalls though: if you ever delete rows in a table with a serial column AND the table gets dropped/recreated (resetting the serial counter back to zero), it may begin reusing serial values that have been previously assigned. This will create problems if any other tables still reference the original serial values. -- Jeff Sturm ZenaComp Incorporated jsturm@zenacomp.com