Last modified on 4 March 2014, at 07:24

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

A short hint: In most cases auto-increment columns are used as Primary Key columns. In the SQL standard the junction of the two concepts is not mandatory.

SQL StandardEdit

The SQL standard defines two ways to generate auto-increment values. First, there are identity columns as an extention to exact numeric types. The syntax is: "GENERATED { ALWAYS | BY DEFAULT } AS IDENTITY". Second, the use of sequences in combination with triggers is standardized.

CREATE TABLE t1 (col1 DECIMAL GENERATED ALWAYS AS IDENTITY);

DB2Edit

Identity columns or sequences combined with triggers (comparison of both techniques).

CREATE TABLE t1 (col1 INT GENERATED ALWAYS AS IDENTITY);
 
--  or:
 
CREATE TABLE t1 (col1 INT);
CREATE SEQUENCE sequence_name;
CREATE TRIGGER insert_trigger
       NO CASCADE BEFORE INSERT ON t1
       REFERENCING NEW AS n
       FOR EACH ROW
  SET n.col1 = NEXTVAL FOR sequence_name;

FirebirdEdit

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

SET TERM  ^;
CREATE TABLE t1(col1 INTEGER NOT NULL PRIMARY KEY)^
CREATE SEQUENCE sequence_name^
ALTER  SEQUENCE sequence_name RESTART WITH 0^
 
CREATE TRIGGER trigger_name FOR t1
BEFORE INSERT
AS
BEGIN
  NEW.col1 = NEXT VALUE FOR sequence_name;
END^

LinterEdit

AUTOINC columns (maybe with RANGEs) or sequences combined with triggers.

CREATE TABLE t1 (col1 SMALLINT AUTOINC);
CREATE TABLE t2 (col1 INTEGER AUTOINC);
CREATE TABLE t3 (col1 BIGINT AUTOINC);

MonetDBEdit

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

MSSQLEdit

CREATE TABLE t1 (col1 INT IDENTITY(1,1));

MySQLEdit

CREATE TABLE t1 (col1 INT NOT NULL PRIMARY KEY AUTO_INCREMENT);

OpenLink VirtuosoEdit

IDENTITY (START WITH 1, INCREMENT BY 1);
CREATE TABLE t1 (col1 INTEGER IDENTITY);
 
-- or:
 
CREATE TABLE t1 (col1 INTEGER IDENTITY (START WITH 1));

OracleEdit

CREATE TABLE t1 (col1 NUMBER PRIMARY KEY);
CREATE SEQUENCE sequence_name START WITH 1 INCREMENT BY 1;
CREATE OR REPLACE TRIGGER trigger_name BEFORE INSERT ON t1 FOR EACH ROW
BEGIN
  SELECT sequence_name.NEXTVAL INTO :NEW.col1 FROM dual;
END;
 
-- since Oracle 12.1:
CREATE TABLE t1 (col1 NUMBER GENERATED BY DEFAULT AS IDENTITY);

PostgreSQLEdit

CREATE TABLE t1 (col1 SERIAL PRIMARY KEY);

SQLiteEdit

CREATE TABLE t1 (col1 INTEGER AUTO_INCREMENT NOT NULL PRIMARY KEY);