Serial Types

The data types SERIAL32 and SERIAL64 are not true types, but merely a notation convenience for creating a unique identifier column (similar to AUTOINCREMENT property).

In the current implementation, specifying:

CREATE TABLE tblName(
    fldID SERIAL32
);

is equivalent to specifying:

CREATE SEQUENCE tblName_fldName_seq;
 
CREATE TABLE tblName(
    fldID LONG NOT NULL DEFAULT METHOD( 'nextval(''tblName_fldName_seq'')' )
);

Thus, we have created a 32-bit integer column and arranged for its default values to be assigned from a sequence generator. A NOT NULL constraint is applied to ensure that a null value cannot be inserted. (In most cases you would also want to attach a UNIQUE or PRIMARY KEY constraint to prevent duplicate values from being inserted by accident, but this is not automatic). Note, that a SERIAL column owns created sequence, so it will be dropped if the column or table is dropped.

To insert the next value of the sequence into the serial column, specify that the serial column should be assigned its default value. This can be done either by excluding the column from the list of columns in the INSERT statement, or through the use of the DEFAULT key word.

Note:

If you need more control over values of the field, you can use prepared sequence. See CREATE SEQUENCE, DROP SEQUENCE.

Note:

We have provide Serial32 and Serial64 types only. There is no Serial8, Serial, BigSerial, SmallSerial aliases, like in PostgreSQL, to not produce garbage.

Note:

To create Foreign Key pointing to a SERIAL32 field, you should use LONG type. For SERIAL64, use LLONG type.