Oracle INSERT ALL语句触发唯一约束违反错误原因咨询

首先是创建表与序列的SQL代码：

CREATE TABLE dept 
(
    dept_id NUMBER PRIMARY KEY, 
    dept_name VARCHAR2(50) 
);

CREATE TABLE emp 
(
    emp_id NUMBER PRIMARY KEY, 
    emp_name VARCHAR2(50), 
    dept_id NUMBER,
    CONSTRAINT emp_fk FOREIGN KEY (dept_id)
        REFERENCES dept (dept_id) 
);

CREATE SEQUENCE myseq1 NOCACHE;

执行以下插入语句时触发了"unique constraint violated"错误：

INSERT ALL INTO emp (emp_id, emp_name)
VALUES (myseq1.nextVal, 'name1') -- name1 insertion
INTO dept (dept_id, dept_name)
VALUES (10, 'dept1') -- dept1 insertion
INTO emp (emp_id, emp_name, dept_id)
VALUES (myseq1.nextVal, 'name2', 10) -- name2 insertion

SELECT * FROM dual;

问题：执行上述SQL语句时遇到唯一约束违反错误，需解释该错误发生的位置及具体原因。

已查阅Oracle官方支持文档，并在Oracle SQL Developer中执行相关查询，但仍未明确错误根源。

• 错误位置：emp表的emp_id主键约束
• 具体原因：Oracle的INSERT ALL语句中，序列的nextVal只会被计算一次。也就是说，两条插入emp表的语句里，myseq1.nextVal返回的是同一个值，导致两条emp记录的emp_id重复，直接违反了主键的唯一性要求。

举个例子：假设序列初始值为1，那么两次插入emp的语句都会使用1作为emp_id，显然不符合主键唯一的规则。

如果要解决这个问题，可以采用以下几种方式：

1. 拆分插入语句，分开插入emp和dept的数据，确保每次插入emp时都能获取新的序列值；
2. 使用UNION ALL结合序列实现多记录插入，示例如下：

INSERT INTO emp (emp_id, emp_name, dept_id)
SELECT myseq1.nextVal, 'name1', NULL FROM dual
UNION ALL
SELECT myseq1.nextVal, 'name2', 10 FROM dual;

INSERT INTO dept (dept_id, dept_name) VALUES (10, 'dept1');

3. 通过PL/SQL循环逐个插入，保证每次调用nextVal都能生成新的序列值。

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