SERIAL(n)

The SERIAL data type stores a sequential integer, in the positive range of the INT8 data type, that is automatically assigned by the database server when a new row is inserted. A table can have no more than one SERIAL column, but it can have one SERIAL and one SERIAL8 column.

SERIAL values in a column are not automatically unique. You must apply a unique index or primary key constraint to this column to prevent duplicate serial numbers. If you use the interactive schema editor in DB–Access to define the table, a unique index is applied automatically to a SERIAL column.

SERIAL numbers might not be consecutive, because of concurrent users, rollbacks, and other factors.

The DEFINE variable LIKE column syntax of SPL for indirect typing declares a variable of the INTEGER data type if column is a SERIAL data type.

The default serial starting number is 1, but you can assign a non-default initial value, n, when you create or alter the table. Any number greater than 0 can be your starting number. The maximum SERIAL is 2,147,483,647. If you assign a number greater than 2,147,483,647, you receive a syntax error. (Use the SERIAL8 data type, rather than SERIAL, if you need a larger range.)

After a nonzero number is assigned, it cannot be changed. You can insert a value into a SERIAL column (using the INSERT statement) or reset a serial column (using the ALTER TABLE statement), if the new value does not duplicate any existing value in the column. To insert into a SERIAL column, your database server increments by one the previous value (or the reset value, if that is larger) and assigns the result as the entered value. If ALTER TABLE has reset the next value of a SERIAL column to a value smaller than values already in that column, however, the next value follows this formula:

(maximum existing value in SERIAL column) + 1

For example, if you reset the serial value of customer.customer_num to 50, when the largest existing value is 128, the next assigned number will be 129. For more details on SERIAL data entry, see the IBM Informix: Guide to SQL Syntax.

A SERIAL column can store unique codes (for example, order, invoice, or customer numbers). SERIAL data values require four bytes of storage, and have the same precision as the INTEGER data type. For details of another way to assign unique whole numbers to each row of a database table, see the CREATE SEQUENCE statement in IBM Informix: Guide to SQL Syntax.