SERIAL data type

Informix®

Informix supports the SERIAL, BIGSERIAL data types to produce automatic integer sequences:
  • SERIAL can produce 32 bit integers (INTEGER)
  • BIGSERIAL can produced 64 bit integers (BIGINT)
  • SERIAL8 is a synonym for BIGSERIAL
Steps to use serials with Informix:
  1. Create the table with a column using SERIAL, or BIGSERIAL.
  2. To generate a new serial, no value or a zero value is specified in the INSERT statement:
    INSERT INTO tab1 ( c ) VALUES ( 'aa' )
    INSERT INTO tab1 ( k, c ) VALUES ( 0, 'aa' )
  3. After INSERT, the new value of a SERIAL column is provided in SQLCA.SQLERRD[2], while the new value of a BIGSERIAL value must be fetched with a SELECT dbinfo('bigserial') query.

Informix allows you to insert rows with a value different from zero for a serial column. Using an explicit value will automatically increment the internal serial counter, to avoid conflicts with future INSERT statements that are using a zero value:

CREATE TABLE tab ( k SERIAL); -- internal counter = 0
INSERT INTO tab VALUES ( 0 ); -- internal counter = 1
INSERT INTO tab VALUES ( 10 ); -- internal counter = 10
INSERT INTO tab VALUES ( 0 ); -- internal counter = 11
DELETE FROM tab; -- internal counter = 11
INSERT INTO tab VALUES ( 0 ); -- internal counter = 12

Oracle® MySQL and MariaDB

MySQL supports the AUTO_INCREMENT column definition option as well as the SERIAL keyword:

  • In CREATE TABLE, you specify a auto-incremented column with the AUTO_INCREMENT attribute

  • Auto-incremented columns have the same behavior as Informix SERIAL columns

  • A start value can be defined with ALTER TABLE tabname AUTO_INCREMENT = value

  • The column must be the primary key, or the first column of an index.

  • When using the InnoDB engine, with MySQL 5.7 and earlier, auto-incremented columns might reuse unused sequences after a server restart. For example, if you insert rows that generate the numbers 101, 102 and 103, then you delete rows 102 and 103; When the server is restarted, next generated number will be 101 + 1 = 102. Starting with MySQL 8.0, the last auto-incremented value is written to the disk and persists across server restarts.

  • SERIAL is a synonym for BIGINT UNSIGNED NOT NULL AUTO_INCREMENT UNIQUE.

Solution

Note: For best SQL portability when using different type of databases, consider using sequences as described in Solution 3: Use native SEQUENCE database objects.

The Informix SERIAL data type is emulated with MySQL AUTO_INCREMENT option.

The serial types emulation can be enabled or disabled with the folllowing FGLPROFILE entries:
dbi.database.dbname.ifxemul.datatype.serial = {true|false}
dbi.database.dbname.ifxemul.datatype.serial8 = {true|false}
dbi.database.dbname.ifxemul.datatype.bigserial = {true|false}

After an insert, SQLCA.SQLERRD[2] holds the last generated serial value. However, SQLCA.SQLERRD[2] is defined as an INTEGER, it cannot hold values from BIGINT auto incremented columns. If you are using BIGINT auto incremented columns, you must use the LAST_INSERT_ID() SQL function.

AUTO_INCREMENT columns must be primary keys. This is handled automatically when you create a table in a BDL program.

Like Informix, MySQL allows you to specify a zero for auto-incremented columns. However, for SQL portability, it is recommended to review INSERT statements to remove the SERIAL column from the list.

For example, the following statement:
INSERT INTO tab (col1,col2) VALUES ( 0, p_value)
can be converted to:
INSERT INTO tab (col2) VALUES (p_value)

Static SQL INSERT using records defined from the schema file must also be reviewed:

DEFINE rec LIKE tab.*
INSERT INTO tab VALUES ( rec.* ) -- will use the serial column 

can be converted to:

INSERT INTO tab VALUES rec.* -- without parentheses, serial column is removed