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.
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 | +----+---+