SERIAL8(n)

The SERIAL8 data type stores a sequential integer, in the positive range of the INT8 data type, that is assigned automatically by the database server when a new row is inserted. It behaves like the SERIAL data type, but with a larger range. (For more information on how to insert values into SERIAL8 columns, see the IBM Informix: Guide to SQL Syntax.)

A SERIAL8 data column is commonly used to store large, unique numeric codes (for example, order, invoice, or customer numbers). SERIAL8 data values have the same precision and storage requirements as INT8 values (page INT8 ). The following restrictions apply to SERIAL8 columns:

• You can define only one SERIAL8 column in a table.

  A table, however, can have one SERIAL8 and one SERIAL column.

• SERIAL8 column values are not automatically unique.

  You must apply a unique index or primary key constraint to this column to prevent duplicate SERIAL8 numbers.

• The SERIAL8 data type does not allow a negative, zero, or NULL value.

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

Assigning a Starting Value for SERIAL8

The default serial starting number is 1, but you can assign an initial value, n, when you create or alter the table. To start the values at 1 in a SERIAL8 column of a table, give the value 0 for the SERIAL8 column when you insert rows into that table. The database server will assign the value 1 to the SERIAL8 column of the first row of the table. The largest SERIAL8 value that you can assign is 2⁶³-1 (9,223,372,036,854,775,807). If you assign a value greater than this, you receive a syntax error. When the database server generates a SERIAL8 value of this maximum number, it wraps around and starts generating values beginning at 1.

After a nonzero SERIAL8 number is assigned, it cannot be changed. You can, however, insert a value into a SERIAL8 column (using the INSERT statement) or reset the SERIAL8 value n (using the ALTER TABLE statement), as long as that value does not duplicate any existing values in the column.

When you insert a number into a SERIAL8 column or reset the next value of a SERIAL8 column, your database server assigns the next number in sequence to the number entered. If you reset the next value of a SERIAL8 column to a value that is less than the values already in that column, however, the next value is computed using the following formula:

maximum existing value in SERIAL8 column + 1

For example, if you reset the SERIAL8 value of the customer_num column in the customer table to 50, when the highest-assigned customer number is 128, the next customer number assigned is 129.

Using SERIAL8 with INT8

All the arithmetic operators that are valid for INT8 (such as +, -, *, and /) and all the SQL functions that are valid for INT8 (such as ABS, MOD, POW, and so on) are also valid for SERIAL8 values. Data conversion rules that apply to INT8 also apply to SERIAL8, but with a NOT NULL constraint on SERIAL8.

The value of a SERIAL8 column of one table can be stored in an INT8 columns of another table. In the second table, however, the INT8 values are not subject to the constraints on the original SERIAL8 column.