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

From Wikibooks, open books for an open world
Jump to: navigation, search

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 Standard[edit]

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

DB2[edit]

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;

Firebird[edit]

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^

Linter[edit]

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

MonetDB[edit]

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

MSSQL[edit]

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

MySQL[edit]

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

OpenLink Virtuoso[edit]

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

Oracle[edit]

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

PostgreSQL[edit]

CREATE TABLE t1 (col1 SERIAL PRIMARY KEY);

SQLite[edit]

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