[SOLVED] Unique Constraint violation

Unique Constraint violation

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;

Now examine this statement:

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;

Question: when I ran this SQL, I get an error about "unique constraint violated". Can someone please explain me where and how unique key is violated?

Thanks in advance!

I tried reading oracle support document, tried running query in Oracle SQL Developer. Now I'm not getting the reason behind that error.

Solution

sequence.NEXTVAL in INSERT ALL is evaluated only once. Either you change to multi INSERT, either you wrap the sequence.NEXTVAL in a function.