SERIAL data type

Informix^® supports the SERIAL, SERIAL8 and BIGSERIAL data types to produce automatic integer sequences. SERIAL is based on INTEGER (32 bit), while SERIAL8 and BIGSERIAL can store 64 bit integers:

The table column must be of type SERIAL, SERIAL8 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 SERIAL value is provided in SQLCA.SQLERRD[2], while the new SERIAL8 and 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

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
You define a start value with ALTER TABLE tabname AUTO_INCREMENT = value
The column must be the primary key.
When using the InnoDB engine, AUTO_INCREMENTED columns might reuse unused sequences after a server restart. Actually, when the server restarts, it issues a SELECT MAX(auto_increment_column) on each table with such as column to identify the next sequence to be generated. 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.
SERIAL is a synonym for BIGINT UNSIGNED NOT NULL AUTO_INCREMENT UNIQUE.

Solution

The Informix SERIAL data type is emulated with MySQL AUTO_INCREMENT option. 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 to specify a zero for auto-incremented columns, however, for SQL portability, INSERT statements should be reviewed 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 braces, serial column is removed