Oracle 11g以前 Aato_increment

Oracle 11g 開始， Oracle 中沒有“auto_increment”或“identity”列這樣的東西。

可以使用序列和触發器輕鬆地對其建模：

創建資料庫

CREATE TABLE SYSTEM_STATUS_ROW(
  ROW_ID    NUMBER(38,0)    NOT NULL  PRIMARY KEY,

  SERVICE   VARCHAR2(50)  NOT NULL,

  CREATEDAT TIMESTAMP    NOT NULL,

  ISALIVE   NUMBER(38,0) DEFAULT 0,

  RESPONSECODE NUMBER

);

程序列

CREATE SEQUENCE SYSTEM_STAUTS_LOG_ROW INCREMENT BY 1 START WITH 1;

觸發程式(tigger)

CREATE OR REPLACE TRIGGER SYSTEM_STAUTS_LOG_ROW 

BEFORE INSERT ON SYSTEM_STAUTS_LOG 
FOR EACH ROW
BEGIN
  SELECT SYSTEM_STATUS_ROW.NEXTVAL
  INTO   :new.row_id
  FROM   dual;
END;
-------------------------------------------
IDENTITY專欄現已在 Oracle 12c 上可用：
create table t1 (
    c1 NUMBER GENERATED by default on null as IDENTITY,
    c2 VARCHAR2(10)
    );

create table t1 (
    c1 NUMBER GENERATED ALWAYS as IDENTITY(START with 1 INCREMENT by 1),
    c2 VARCHAR2(10)
    );
------------------------------------------
或者，Oracle 12 還允許使用序列作為默認值：

CREATE SEQUENCE dept_seq START WITH 1;

CREATE TABLE departments (
  ID           NUMBER(10)    DEFAULT dept_seq.nextval NOT NULL,
  DESCRIPTION  VARCHAR2(50)  NOT NULL);

ALTER TABLE departments ADD (
  CONSTRAINT dept_pk PRIMARY KEY (ID));