sqloracle-database

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.