Serial types

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 poslng ]
    [ INCREMENT BY nonzerolng ]
    [ MINVALUE nonzerolng | NO MINVALUE ]
    [ MAXVALUE nonzerolng | NO MAXVALUE ]
    [ CACHE nonzerolng ]
    [ [ 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 |
     +----+---+