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

From Wikibooks, the open-content textbooks collection

[edit] 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 / Interbase:

CREATE table (column INTEGER NOT NULL PRIMARY KEY);;

CREATE GENERATOR table_gen;;
SET GENERATOR table_gen TO 0;;

CREATE TRIGGER t_column_gen FOR table
BEFORE INSERT
AS
BEGIN
  NEW.column = GEN_ID(table_gen, 1);
END;; 

• 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);