Kluge with serial values--Is it safe to use negative values in the
Posted in 2003
Due to circumstances beyond my control, and constraints that are 'challenging', we are considering a kluge using serial values. It is ugly and trust that it will be a last resort. I am in essence creating a gap in the serial values and letting the serial next value work as it normally does, and 'manually' inserting non-duplicate values in the gap. In my tests I have noticed that negative serial values can be inserted into the serial fields and APPEAR to cause no problems (and they do not mess with the next serial value). Is it safe to use negative values in serial fields? Thanks for your help! Sam For your reference: From Guide to SQL :Syntax Inserting Values into Serial Columns You can insert consecutive numbers, explicit values, or explicit values that reset the serial sequence value in a serial column: To insert a consecutive serial value Specify a zero (0) for the serial column in the INSERT statement. In this case, the database server assigns the next highest value. To insert an explicit value Specify the nonzero value after first verifying that it does not duplicate one already in the table. If the serial column is uniquely indexed or has a unique constraint, and you try to insert a value that duplicates one already in the table, the database server returns an error. If the value is greater than the current maximum value, you will create a gap in the sequence. To create a gap in the sequence (reset the serial value) Specify a positive value that is greater than the current maximum value in the column. As an alternative, you can use the MODIFY clause of the ALTER TABLE statement to reset the next value of a serial column. For more information, see Altering the Next Serial Number on page 2-67.