SQL Dialects Reference/Data structure definition/Auto-increment column

Auto-increment column

• DB2: Identity columns or sequences combined with triggers (comparison of both techniques)

hh
CREATE TABLE table_name ( column_name INT GENERATED ALWAYS AS IDENTITY )

or

CREATE SEQUENCE sequence_name;

CREATE TABLE table_name ( column_name INT );

CREATE TRIGGER insert_trigger
   NO CASCADE BEFORE INSERT ON table_name
   REFERENCING NEW AS n
   FOR EACH ROW
   SET n.column_name = NEXTVAL FOR sequence_name;

• Firebird: Is recommended to use sequences combined with triggers . From 3.0 there is Identity support

SET TERM  ^;
CREATE table table_name(column_name INTEGER NOT NULL PRIMARY KEY)^
CREATE SEQUENCE column_name_seq^
ALTER SEQUENCE column_name_seq RESTART WITH 0^

CREATE TRIGGER column_name_seq FOR table_name
BEFORE INSERT
AS
BEGIN
  NEW.column_name = NEXT VALUE FOR column_name_seq;
END^ 

• MonetDB:

CREATE SEQUENCE seq AS INT START WITH 1 INCREMENT BY 1;
CREATE TABLE table1 (col1 INT PRIMARY KEY DEFAULT NEXT VALUE FOR seq,
                col2 INT AUTO INCREMENT, 
                col1 INT GENERATED ALWAYS AS IDENTITY (
                START WITH 100 INCREMENT BY 2
                NO MINVALUE MAXVALUE 1000
                CACHE 2 CYCLE))

• MySQL: CREATE TABLE table (column INT NOT NULL PRIMARY KEY AUTO_INCREMENT)
• MSSQL: CREATE TABLE table (column INT IDENTITY(1,1))
• Oracle:

CREATE TABLE table (column NUMBER PRIMARY KEY);
CREATE SEQUENCE sequence START WITH 1 INCREMENT BY 1;
CREATE OR REPLACE TRIGGER trigger BEFORE INSERT ON table REFERENCING NEW AS NEW FOR EACH ROW
BEGIN
  SELECT sequence.nextval INTO :NEW.column FROM dual;
END;

• PostgreSQL: CREATE TABLE table (column SERIAL PRIMARY KEY)
• OpenLink Virtuoso

IDENTITY (start with 1, increment by 1);
CREATE TABLE table (column INTEGER IDENTITY);

or

CREATE TABLE table (column INTEGER IDENTITY (start with 1));

• SQLite:

CREATE TABLE table (column INTEGER AUTO_INCREMENT NOT NULL PRIMARY KEY)

• Linter- AUTOINC columns (maybe with RANGEs) or sequences combined with triggers

CREATE TABLE table (column SMALLINT AUTOINC);
CREATE TABLE table (column INTEGER AUTOINC);
CREATE TABLE table (column BIGINT AUTOINC);