SERIAL Data Type
A field with a SERIAL (%Library.CounterOpens in a new tab) data type can take a user-specified positive integer value, or InterSystems IRIS can assign it a sequential positive integer value. %Library.CounterOpens in a new tab extends %Library.BigIntOpens in a new tab.
An INSERT operation specifies one of the following values for a SERIAL field:
-
No value, 0 (zero), or a nonnumeric value: InterSystems IRIS ignores the specified value, and instead increments this field's current serial counter value by 1, and inserts the resulting integer into the field.
-
A positive integer value: InterSystems IRIS inserts the user-specified value into the field, and changes the serial counter value for this field to this integer value.
Thus a SERIAL field contains a series incremental integer values. These values are not necessarily continuous or unique. For example, the following is a valid series of values for a SERIAL field: 1, 2, 3, 17, 18, 25, 25, 26, 27. Sequential integers are either InterSystems IRIS-generated or user-supplied; nonsequential integers are user-supplied. If you wish SERIAL field values to be unique, you must apply a UNIQUE constraint on the field.
An UPDATE operation has no effect on automatically-assigned SERIAL counter field values. However, an update performed using INSERT OR UPDATE causes a skip in integer sequence for subsequent insert operations for a SERIAL field.
An UPDATE operation can only change a serial field value if the field currently has no value (NULL), or its value is 0. Otherwise, an SQLCODE -105 error is generated.
InterSystems IRIS imposes no restriction on the number of SERIAL fields in a table.
