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

< SQL Dialects Reference

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.

Contents

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
DECLARE
  max_id NUMBER;
  cur_seq NUMBER;
BEGIN
IF :NEW.col1 IS NULL THEN
  -- normal assignment of the next value in the sequence
  :NEW.col1 := sequence_name.NEXTVAL;
ELSE
  -- or allow the user to specify the value, so must advance the sequence to match
  SELECT GREATEST(COALESCE(MAX(col1), 0), :NEW.col1) INTO max_id FROM t1;
  WHILE cur_seq < max_id LOOP
    SELECT sequence_name.NEXTVAL INTO cur_seq FROM DUAL;
  END LOOP;
END IF;
END;

-- since Oracle 12.1:
CREATE TABLE t1 (col1 NUMBER GENERATED BY DEFAULT AS IDENTITY);

PostgreSQLEdit

CREATE TABLE t1 (col1 SERIAL PRIMARY KEY);

SQLiteEdit

Both create an autoincrementing column; the AUTOINCREMENT keyword only prevents reusing deleted values.

CREATE TABLE t1 (col1 INTEGER PRIMARY KEY);
CREATE TABLE t1 (col1 INTEGER PRIMARY KEY AUTOINCREMENT);