< SQL Dialects Reference < Data structure definition

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

The SQL standard defines two ways to generate auto-increment values. First, there are identity columns as an extension 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

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

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

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

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

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

MySQL

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

IDENTITY (start with 1, increment by 1);
CREATE TABLE t1 (col1 INTEGER IDENTITY);

-- or:

CREATE TABLE t1 (col1 INTEGER IDENTITY (start with 1));

Oracle

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

PostgreSQL

CREATE TABLE t1 (col1 SERIAL PRIMARY KEY);

SQLite

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);
This article is issued from Wikibooks. The text is licensed under Creative Commons - Attribution - Sharealike. Additional terms may apply for the media files.