Oracle 自增列与 IDENTITY（12c+）

适用版本：Oracle Database 12c / 19c / 23ai 文档版本：v1.0 / 2026-07

---

1. 概述

Oracle 12c 引入 IDENTITY 列[1]，简化自增主键实现：

对比：

方式	说明
序列 + 触发器	12c 之前
IDENTITY	12c+ 原生支持

---

2. IDENTITY 语法

2.1 GENERATED ALWAYS

CREATE TABLE employees (
  id NUMBER GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  name VARCHAR2(100)
);

-- 不能手动指定 ID
INSERT INTO employees (name) VALUES ('Alice');
-- INSERT INTO employees (id, name) VALUES (1, 'Alice');  -- 错误

2.2 GENERATED BY DEFAULT

CREATE TABLE employees (
  id NUMBER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
  name VARCHAR2(100)
);

-- 可手动指定
INSERT INTO employees (id, name) VALUES (100, 'Alice');
INSERT INTO employees (name) VALUES ('Bob');  -- 自动

2.3 GENERATED BY DEFAULT ON NULL

CREATE TABLE employees (
  id NUMBER GENERATED BY DEFAULT ON NULL AS IDENTITY PRIMARY KEY,
  name VARCHAR2(100)
);

-- NULL 时自动生成
INSERT INTO employees (id, name) VALUES (NULL, 'Alice');

---

3. 序列属性

CREATE TABLE employees (
  id NUMBER GENERATED ALWAYS AS IDENTITY (
    START WITH 100
    INCREMENT BY 1
    MAXVALUE 1000000
    NOMINVALUE
    NOCYCLE
    CACHE 100
    NOORDER
  ) PRIMARY KEY,
  name VARCHAR2(100)
);

---

4. 修改 IDENTITY

4.1 修改序列属性

ALTER TABLE employees MODIFY (
  id GENERATED ALWAYS AS IDENTITY (
    INCREMENT BY 10
    CACHE 200
  )
);

4.2 修改为 GENERATED BY DEFAULT

ALTER TABLE employees MODIFY (
  id GENERATED BY DEFAULT AS IDENTITY
);

---

5. 查看 IDENTITY

SELECT 
  table_name,
  column_name,
  generation_type,
  identity_options
FROM user_tab_identity_cols
WHERE table_name = 'EMPLOYEES';

---

6. 底层序列

-- IDENTITY 内部使用序列
SELECT sequence_name FROM user_sequences;
-- ISEQ$$<object_id>

---

7. 与触发器对比

7.1 触发器方式

-- 序列
CREATE SEQUENCE seq_emp_id START WITH 1;

-- 触发器
CREATE OR REPLACE TRIGGER trg_emp_id
BEFORE INSERT ON employees
FOR EACH ROW
BEGIN
  IF :NEW.id IS NULL THEN
    :NEW.id := seq_emp_id.NEXTVAL;
  END IF;
END;
/

-- 表
CREATE TABLE employees (
  id NUMBER PRIMARY KEY,
  name VARCHAR2(100)
);

7.2 对比

维度	触发器	IDENTITY
复杂度	高	低
性能	中	好
维护	多对象	一处
手动控制	是	BY DEFAULT

---

8. 迁移

8.1 触发器 → IDENTITY

-- 1. 获取当前序列值
SELECT seq_emp_id.CURRVAL FROM dual;

-- 2. 创建新表
CREATE TABLE employees_new (
  id NUMBER GENERATED BY DEFAULT AS IDENTITY (
    START WITH <cur_val>
  ) PRIMARY KEY,
  name VARCHAR2(100)
);

-- 3. 迁移数据
INSERT INTO employees_new SELECT * FROM employees;

-- 4. 重命名
DROP TABLE employees;
RENAME employees_new TO employees;

---

9. 常见坑与排错

9.1 ORA-32795: 不能插入

-- GENERATED ALWAYS 不能手动插入
-- 修改为 BY DEFAULT
ALTER TABLE employees MODIFY (id GENERATED BY DEFAULT AS IDENTITY);

9.2 序列值跳

-- 正常现象
-- 1. 实例崩溃缓存丢失
-- 2. 回滚事务

9.3 ORA-30649: 缺少 START WITH

-- 修改已有 IDENTITY 时
ALTER TABLE employees MODIFY (
  id GENERATED BY DEFAULT AS IDENTITY (START WITH 1000)
);

---

10. 最佳实践

1. 12c+ 优先 IDENTITY：简化
2. BY DEFAULT 允许手动：灵活
3. CACHE 提升性能：高并发
4. 主键标配：自增
5. 迁移用 BY DEFAULT：兼容
6. 定期收集统计信息：CBO

---

11. 参考资料

[1] Oracle Database SQL Language Reference 19c, “Identity Columns” https://docs.oracle.com/en/database/oracle/oracle-database/19/sqlrf/CREATE-TABLE.html