Oracle 自增主鍵實作全攻略：Sequence + Trigger 經典模式與 12c Identity Column 新寫法

Oracle 無原生 AUTO_INCREMENT，傳統方案為 Sequence + BEFORE INSERT Trigger；12c 引入 IDENTITY 欄位原生支援自增。本文完整示範兩種模式的建立、使用、效能差異、遷移策略，並解析 Sequence 核心參數與快取機制。

一、傳統模式：Sequence + Trigger

1. 建立表與 Sequence

CREATE TABLE example (
  id     NUMBER(10) NOT NULL PRIMARY KEY,
  name   VARCHAR2(25),
  phone  VARCHAR2(10),
  address VARCHAR2(50)
);

CREATE SEQUENCE emp_sequence
  INCREMENT BY 1
  START WITH 1
  NOMAXVALUE
  NOCYCLE
  CACHE 20;   -- 關鍵：快取 20 個值於 SGA，大幅降低序號產生鎖競爭

2. 建立 Trigger

CREATE OR REPLACE TRIGGER trg_example_auto_id
BEFORE INSERT ON example
FOR EACH ROW
WHEN (NEW.id IS NULL)
BEGIN
  SELECT emp_sequence.NEXTVAL INTO :NEW.id FROM dual;
END;
/

3. 測試插入

INSERT INTO example (name, phone, address) VALUES ('Cao', '56498543', 'Hebei');
INSERT INTO example (name, phone, address) VALUES ('Liu', '13800001111', 'Beijing');
SELECT * FROM example;

二、12c+ 原生模式：IDENTITY Column

CREATE TABLE example_new (
  id     NUMBER(10) GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  name   VARCHAR2(25),
  phone  VARCHAR2(10),
  address VARCHAR2(50)
);
-- 或允許手動指定 ID（GENERATED BY DEFAULT）
CREATE TABLE example_flex (
  id     NUMBER(10) GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
  ...
);

• GENERATED ALWAYS：不可手動指定 ID，違例報錯。
• GENERATED BY DEFAULT：可手動指定，未指定時自動取序號。
• 內部自動建立同名 Sequence（ISEQ$$_），可透過 USER_TAB_IDENTITY_COLS 查詢。

三、Sequence 核心參數深度解析

四、效能對比：Trigger vs Identity

五、常見坑點與避坑指南

1. Trigger 中忘記 WHEN 條件：導致手動指定 ID 時仍被覆蓋。
   WHEN (NEW.id IS NULL) 必加。
2. Sequence CACHE 過大 + 實例崩潰：重啟後序號跳躍，業務若要求連續需設 NOCACHE（效能大幅下降）。
3. RAC 環境未設 ORDER：各節點各自快取，全域序號不連續、甚至倒序，導致主鍵衝突或業務邏輯錯誤。
4. 多表共用單一 Sequence：可行但序號跨表不連續；若需表級連續請各自建 Sequence。
5. Identity Column 無法 ALTER MODIFY：12c 不支援將既有欄位改為 IDENTITY，需重建表或用 DBMS_METADATA 導出重建。

六、遷移腳本：傳統表改為 Identity（12c+）

-- 1. 重建表
CREATE TABLE example_new AS SELECT * FROM example WHERE 1=0;
ALTER TABLE example_new MODIFY (id GENERATED ALWAYS AS IDENTITY);

-- 2. 複製資料（ID 保留）
INSERT INTO example_new SELECT * FROM example;

-- 3. 切換
RENAME example TO example_old;
RENAME example_new TO example;
-- 重建索引、約束、授權、觸發器

選擇 Sequence+Trigger 還是 Identity，取決於版本、相容性、效能敏感度。新專案 12c+ 首選 Identity；既有系統若無效能痛點，維持現狀即可。核心原則：主鍵序號唯一、不連續可接受、CACHE 與 ORDER 根據架構正確配置。