插入手动ID后如何正确设置GENERATED BY DEFAULT AS IDENTITY序列？

问题场景 #

创建带IDENTITY主键的表：

CREATE TABLE test (
    id INT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
    data VARCHAR
);

手动插入指定ID的数据：

INSERT INTO test(id,data) VALUES (3,'something');
INSERT INTO test(id,data) VALUES (6,'something else');

尝试自动生成ID插入时触发主键冲突：

INSERT INTO test(data) VALUES ('whatever');

报错：

ORA-00001: unique constraint (FIDDLE_CEYTNFUWNIDRFXSPTDWJ.SYS_C0054804) violated

原因 #

IDENTITY特性依赖的序列仍从初始默认值（通常为1）开始生成ID，与已手动插入的ID重复，触发主键唯一约束。

解决步骤 #

1. 定位IDENTITY对应的序列名

执行以下查询，获取表关联的序列名称：

SELECT sequence_name
FROM user_tab_identity_cols
WHERE table_name = 'TEST';

返回的序列名格式通常为ISEQ$$_<数字>，例如ISEQ$$_12345。

2. 手动重置序列到正确起始值

先查询表中已存在的最大ID：

SELECT MAX(id) FROM test;

假设查询结果为6，执行ALTER语句将序列起始值设为最大ID+1：

ALTER SEQUENCE ISEQ$$_12345 RESTART WITH 7;

注：RESTART WITH是Oracle 12c及以上版本支持的语法。若使用更早版本，可通过以下方式实现：

-- 先调整序列增量到目标差值
ALTER SEQUENCE ISEQ$$_12345 INCREMENT BY 6;
-- 生成一次值，让序列跳到7
SELECT ISEQ$$_12345.NEXTVAL FROM DUAL;
-- 恢复序列默认增量
ALTER SEQUENCE ISEQ$$_12345 INCREMENT BY 1;

3. 自动重置的PL/SQL脚本（推荐）

若不想手动查询和输入，可执行以下PL/SQL块自动完成序列重置：

DECLARE
    v_seq_name VARCHAR2(30);
    v_max_id NUMBER;
BEGIN
    -- 获取IDENTITY对应的序列名
    SELECT sequence_name INTO v_seq_name
    FROM user_tab_identity_cols
    WHERE table_name = 'TEST';

    -- 计算下一个应生成的ID值（表为空则设为1）
    SELECT COALESCE(MAX(id), 0) + 1 INTO v_max_id
    FROM test;

    -- 执行序列重置
    EXECUTE IMMEDIATE 'ALTER SEQUENCE ' || v_seq_name || ' RESTART WITH ' || v_max_id;
END;
/

验证 #

重置后再次执行自动插入语句，即可正常生成不重复的ID：

INSERT INTO test(data) VALUES ('whatever'); -- ID为7
INSERT INTO test(data) VALUES ('stuff'); -- ID为8
INSERT INTO test(data) VALUES ('etc'); -- ID为9

内容的提问来源于stack exchange，提问作者Manngo