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:

Create the table with a column using SERIAL, or BIGSERIAL.
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' )
```
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