ORA-00001:违反唯一约束pk

Question

当我逐行运行该脚本时，该脚本可以工作，但在尝试一次全部运行时，我一直会收到错误消息。

我用的是https://apex.oracle.com/

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;

以下是我的参考表的说明：

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));

Answer 1

是的，这就是它的工作原理(或者，我们应该说，它不起作用)：

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

但是，如果切换到UNION的集合：

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

2 rows created.

因此:要么逐个插入行，要么使用UNION (ALL)。