Automatic sequences: BIGSERIAL, SERIAL, and SERIAL8

The SERIAL data type has the positive non-zero range of an INTEGER with a special feature. Similarly, the BIGSERIAL and SERIAL8 data types have the positive non-zero range of an INT8 with a special feature. Whenever a new row is inserted into a table, the database server automatically generates a new value for BIGSERIAL, SERIAL, or SERIAL8 columns.

A table can have no more than one SERIAL column, but it can have a SERIAL column and either a SERIAL8 column or a BIGSERIAL column. Because the database server generates the values, the serial values in new rows are always different, even when multiple users are adding rows at the same time. This service is useful because it is quite difficult for an ordinary program to coin unique numeric codes under those conditions. (Informix®, however, also supports sequence objects, which can also support this functionality through the CURRVAL and NEXTVAL operators. For more information about sequence objects, see the description of CREATE SEQUENCE in Informix® Guide to SQL: Syntax.

The SERIAL data type can yield up to 2³¹-1 positive integers. Consequently, the database server uses all the positive serial numbers by the time it inserts 2³¹-1 rows in a table. For most users the exhaustion of the positive serial numbers is not a concern, however, because a single application would have to insert a row every second for 68 years, or 68 applications would have to insert a row every second for a year, to use all the positive serial numbers. However, if all the positive serial numbers were used, the database server would wrap around and start to generate integer values that begin with a 1.

The BIGSERIAL and SERIAL8 data types can yield up to 2⁶³ -1 positive integers. With a reasonable starting value, it is virtually impossible to cause a value of these types to wrap around during insertions.

For these data types, the sequence of generated numbers always increases. When rows are deleted from the table, their serial numbers are not reused. Rows that are sorted on columns of these types are returned in the order in which they were created.

You can specify the initial value in a BIGSERIAL, SERIAL, or SERIAL8 column in the CREATE TABLE statement. This makes it possible to generate different subsequences of system-assigned keys in different tables. The stores_demo database uses this technique. In stores_demo, the customer numbers begin at 101, and the order numbers start at 1001. If this small business does not register more than 899 customers, all customer numbers have three digits and order numbers have four.

A BIGSERIAL, SERIAL, or SERIAL8 column is not automatically a unique column. If you want to be perfectly sure that no duplicate serial numbers occur, you must apply a unique constraint (see Use CREATE TABLE). If you define the table using the interactive schema editor in DB-Access, it automatically applies a unique constraint to any BIGSERIAL, SERIAL, or SERIAL8 column.

For information about the use and behavior of SERIAL, SERIAL8, and BIGSERIAL data types in table hierarchies, see SERIAL types in a table hierarchy.