SQL 方言參考/資料結構定義/自動遞增列

一個小提示：在大多數情況下，自動遞增列用作主鍵列。在 SQL 標準中，這兩個概念的結合不是強制性的。

SQL 標準

SQL 標準定義了兩種生成自動遞增值的方法。首先，作為對精確數值型別的擴充套件，存在標識列。語法為：“GENERATED { ALWAYS | BY DEFAULT } AS IDENTITY”。其次，將序列與觸發器結合使用是標準化的。

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

DB2

標識列或序列與觸發器結合使用 (兩種技術的比較)。

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

建議使用 序列 與觸發器結合使用。從 3.0 版本開始，提供 標識 支援。

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 列（可能帶有範圍）或序列與觸發器結合使用。

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 BIGINT PRIMARY KEY DEFAULT NEXT VALUE FOR sequence_name,
  col2 BIGINT AUTO_INCREMENT, 
  col3 BIGINT GENERATED ALWAYS AS IDENTITY (
           START WITH 100 INCREMENT BY 2
           NO MINVALUE MAXVALUE 1000
           CACHE 2 CYCLE)
);
CREATE TABLE ts (
  col1 SERIAL,  /* implies: INTEGER NOT NULL PRIMARY KEY DEFAULT NEXT VALUE FOR "sch"."seq_12345" */
  ...
);
CREATE TABLE tbs (
  col1 BIGSERIAL,  /* implies: BIGINT NOT NULL PRIMARY KEY DEFAULT NEXT VALUE FOR "sch"."seq_23456" */
  ...
);

MSSQL

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

MySQL

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

OpenLink Virtuoso

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

-- since postgres 10:
create table t1 (col1 integer generated by default as identity primary key);

SQLite

兩者都建立一個 自動遞增列；AUTOINCREMENT 關鍵字僅阻止重新使用已刪除的值。

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