Skip to main content

Serial datatypes

As of 2003 the SQL standard supports serial types (sequences). They are of particular use in auto-generating key values.A serial type is defined as a primary database object over any of the built-in data types. The NEXT VALUE operation generates the next value and can be used anywhere a value expression is allowed. Its name should be unique. It can only be dropped when the tables mentioning it have previously been dropped.

generated_column:
    GENERATED ALWAYS AS IDENTITY [ '(' seq_params ')' ]
  | AUTO_INCREMENT

sequence_def:
    SEQUENCE name AS datatype

seq_params:
    [ START WITH nonzero-bigint ]
    [ INCREMENT BY nonzerol-bigint ]
    [ MINVALUE nonzero-bigint | NO MINVALUE ]
    [ MAXVALUE nonzero-bigint | NO MAXVALUE ]
    [ CACHE nonzero-bigint ]
    [ [ NO ] CYCLE ]

Example. The example shown below introduces the column count, which is incremented with each row being added. It is conceptually identical to the value expression max(count)+1in each insert. The column info is a limited range with wrap around.The serial type as found in PostgreSQL and the auto_increment  flag as found in MySQL are both mapped onto a sequence type in MonetDB/SQL.

CREATE TABLE test (
   d date,
   id serial,
   count INT AUTO_INCREMENT,
   info INT GENERATED ALWAYS AS

        IDENTITY (
           START WITH 100 INCREMENT BY 2
           NO MINVALUE MAXVALUE 1000
           CACHE 2 CYCLE
)      );

Much like other primary database objects, the sequence type can be altered at any time as illustrated below.

 

sql>CREATE SEQUENCE "my_test_seq" as integer START WITH 2;
sql>CREATE TABLE test (t int DEFAULT
more> NEXT VALUE FOR "my_test_seq", v char);
sql>INSERT INTO test(v) VALUES ('a');
Rows affected 1
sql>INSERT INTO test VALUES (10, 'b');
Rows affected 1
sql>ALTER SEQUENCE "my_test_seq"
more> RESTART WITH (SELECT MAX(t) + 1 FROM test);
sql>INSERT INTO test(v) VALUES ('c');
Rows affected 1
sql>SELECT * FROM test;
+----+---+
| t  | v |
+====+===+
| 2  | a |
| 10 | b |
| 11 | c |
+----+---+