ORA-00001: unique constraint pk violated

Question

This script works when I run it row by row but I keep getting the error message when attempting to run all at once.

INSERT ALL
       INTO Faculty (First_Name, Last_Name, Office_ID) VALUES ('Patricia', 'Prayor', 64)
       INTO Faculty (First_Name, Last_Name, Office_ID) VALUES ('Winston', 'Palacios', 64)
       INTO Faculty (First_Name, Last_Name, Office_ID) VALUES ('Darren', 'Farmer', 24)
       INTO Faculty (First_Name, Last_Name, Office_ID) VALUES ('Jennivi', 'Fajardo', 25)
       INTO Faculty (First_Name, Last_Name, Office_ID) VALUES ('Brian', 'Willington', 25)
       INTO Faculty (First_Name, Last_Name, Office_ID) VALUES ('Annsarah', 'Tanker', 65)
       INTO Faculty (First_Name, Last_Name, Office_ID) VALUES ('Diane', 'Chester', 67)
       INTO Faculty (First_Name, Last_Name, Office_ID) VALUES ('Samone', 'Ambrose', 65)
       INTO Faculty (First_Name, Last_Name, Office_ID) VALUES ('Ashley', 'Smith', 66)
       INTO Faculty (First_Name, Last_Name, Office_ID) VALUES ('Sampson', 'Baker', 67)
       INTO Faculty (First_Name, Last_Name, Office_ID) VALUES ('Orin', 'Sandy', 66)
       INTO Faculty (First_Name, Last_Name, Office_ID) VALUES ('Trevor', 'Hinds', 24)
       INTO Faculty (First_Name, Last_Name, Office_ID) VALUES ('Jessica', 'Jenner', 24)
       INTO Faculty (First_Name, Last_Name, Office_ID) VALUES ('Shannon', 'Brown', 25)
       INTO Faculty (First_Name, Last_Name, Office_ID) VALUES ('Bernard', 'Davis', 64)
       INTO Faculty (First_Name, Last_Name, Office_ID) VALUES ('Bruce', 'Jackson', 24)
       INTO Faculty (First_Name, Last_Name, Office_ID) VALUES ('Eugene', 'Thomas', 65)
       INTO Faculty (First_Name, Last_Name, Office_ID) VALUES ('Daphne', 'Brandt', 67)
       INTO Faculty (First_Name, Last_Name, Office_ID) VALUES ('Kendall', 'Justice', 67)
SELECT * FROM DUAL;

This is the statement for my reference table:

CREATE TABLE Faculty(
       Faculty_ID INT GENERATED BY DEFAULT AS IDENTITY NOT NULL,
       First_Name VARCHAR(25) NOT NULL,
       Last_Name VARCHAR (25) NOT NULL,
       Office_ID INT NOT NULL,
            CONSTRAINT Faculty_PK PRIMARY KEY (Faculty_ID),
CONSTRAINT Faculty_FK1 FOREIGN KEY (Office_ID) REFERENCES Office(Office_ID));

score 2 · Accepted Answer · answered Mar 29 '20 at 06:03

Yes, that's how it works (or, should we say, doesn't work):

SQL> create table test (id int generated by default as identity primary key, name varchar2(20));

Table created.

SQL> insert all
  2    into test (name) values ('Mike')
  3    into test (name) values ('Scott')
  4  select * from dual;
insert all
*
ERROR at line 1:
ORA-00001: unique constraint (DP_4005.SYS_C001686002) violated

But, if you switch to set of UNIONs:

SQL> insert into test (name)
  2    select 'Little' from dual
  3    union all
  4    select 'Foot' from dual;

2 rows created.

So: either insert rows one-by-one, or use UNION (ALL).

ORA-00001: unique constraint pk violated

1 Answers1