Oracle SQL Developer:插入值时违反了完整性约束（未找到父项）

Question

我被要求解决的最初问题是：

创建两个表：Employee:empID（PK）、empFname、empLname、deptID（FK）和Department:deptID（PK）、deptName、chairID。chairID是Employee表中的empID。在Department表中至少插入3行，在Employee表中至少插入6行。更新chairID时创建触发器，强制执行以下业务规则：一名员工只能主持一个部门。每个系只有一把椅子。

我解决这个问题的办法是：

create table Employee(empID NUMBER,
empFname VARCHAR2(20),
empLname VARCHAR2(20),
deptID NUMBER,
PRIMARY KEY (empID)
);

create table Department(deptID NUMBER,
deptName VARCHAR2(20),
chairID NUMBER,
PRIMARY KEY (deptID));

ALTER TABLE Employee
ADD FOREIGN KEY (deptID) REFERENCES Department(deptID);  

ALTER TABLE Department
ADD FOREIGN KEY (chairID) REFERENCES Employee(empID);   


INSERT INTO DEPARTMENT (deptID ,deptName,chairID ) VALUES (401,'COMPUTER SCIENCE',301);
INSERT INTO DEPARTMENT (deptID ,deptName,chairID ) VALUES (402,'ELECTRONICS',302);
INSERT INTO DEPARTMENT (deptID ,deptName,chairID ) VALUES (403,'MATHEMATICS',303);

INSERT INTO EMPLOYEE (empID ,empFname ,empLname,deptID) VALUES (1001,'Alen','Zer',301);
INSERT INTO EMPLOYEE (empID ,empFname ,empLname,deptID) VALUES (1002,'Beny','Ker',301);
INSERT INTO EMPLOYEE (empID ,empFname ,empLname,deptID) VALUES (1003,'Clen','Ler',302);
INSERT INTO EMPLOYEE (empID ,empFname ,empLname,deptID) VALUES (1004,'Dlen','Mer',302);
INSERT INTO EMPLOYEE (empID ,empFname ,empLname,deptID) VALUES (1005,'Elen','Ner',303);
INSERT INTO EMPLOYEE (empID ,empFname ,empLname,deptID) VALUES (1006,'Flen','Oer',303);

CREATE OR REPLACE TRIGGER chairID_after_update
BEFORE UPDATE
ON Department
FOR EACH ROW

DECLARE
v_username varchar2(10);
v_count number;
v_sql varchar2(50);
BEGIN

select count(*) into v_count from Department where chairID = :new.chairID;
IF v_count = 0 then
v_sql := 'alter table set chairID ='+ :new.chairID;
execute immediate v_sql;
end if;

END;
/

我的表创建和触发器编译，但是当试图插入到我的表中时，我所有的插入语句都会命中：

SQL错误： ORA-02291：完整性约束（SYSTEM.SYS_C0010675）违反-找不到父密钥02291. 00000-"完整性约束（%s.%s）违反-找不到父密钥"*原因：外键值没有匹配的主键值。*操作：删除外键或添加匹配的主键。

我应该如何解决此问题？如有任何帮助/建议，将不胜感激。

Answer 1

一名员工只能主持一个部门。

每个系只有一把椅子。

不需要触发器（除了用于教育目的）-使DEPARTMENT. CHAIRID列UniQUE和NOTNULL。

CREATE TABLE Employee(
  empID    NUMBER
           CONSTRAINT employee__empid__pk PRIMARY KEY,
  empFname VARCHAR2(20),
  empLname VARCHAR2(20),
  deptID   NUMBER       
);

CREATE TABLE Department(
  deptID   NUMBER
           CONSTRAINT department__deptid__pk PRIMARY KEY,
  deptName VARCHAR2(20),
  chairID  NUMBER
           CONSTRAINT department__chairid__nn NOT NULL
           CONSTRAINT department__chairid__u  UNIQUE
           CONSTRAINT department__chairid__fk REFERENCES employee ( empID )
);

ALTER TABLE Employee
ADD CONSTRAINT employee__deptid__fk
    FOREIGN KEY (deptID) REFERENCES Department(deptID);

插入部门ID为NULL的员工，然后创建部门，最后更新员工以包含部门ID：

INSERT INTO EMPLOYEE (empID ,empFname ,empLname,deptID)
  SELECT 1001,'Alen','Zer', NULL FROM DUAL UNION ALL
  SELECT 1002,'Beny','Ker', NULL FROM DUAL UNION ALL
  SELECT 1003,'Clen','Ler', NULL FROM DUAL UNION ALL
  SELECT 1004,'Dlen','Mer', NULL FROM DUAL UNION ALL
  SELECT 1005,'Elen','Ner', NULL FROM DUAL UNION ALL
  SELECT 1006,'Flen','Per', NULL FROM DUAL;

INSERT INTO DEPARTMENT (deptID ,deptName,chairID )
  SELECT 301,'COMPUTER SCIENCE', 1001 FROM DUAL UNION ALL
  SELECT 302,'ELECTRONICS',      1004 FROM DUAL UNION ALL
  SELECT 303,'MATHEMATICS',      1005 FROM DUAL;

MERGE INTO EMLPOYEE dst
USING ( SELECT 301 AS deptid, 1001 AS empID FROM DUAL UNION ALL
        SELECT 301, 1002 FROM DUAL UNION ALL
        SELECT 302, 1003 FROM DUAL UNION ALL
        SELECT 302, 1004 FROM DUAL UNION ALL
        SELECT 303, 1005 FROM DUAL UNION ALL
        SELECT 303, 1006 FROM DUAL ) src
ON ( dst.empId = src.empid )
WHEN MATCHED THEN
  UPDATE SET deptId = src.deptID;

Answer 2

显然你有一个逻辑问题（循环推理）：当没有员工时，你不能先创建一个部门，因为CHAIRID上的外键约束会失败，你不能先创建员工，因为DEPTID上的外键会失败。然而，您的数据模型显然是正确的。

那么，该怎么办？

一种解决方案是不要在一开始就添加外键。执行其他操作，插入数据，然后更改表以添加外键。

一个更好的解决方案是从一开始就添加FK，但使它们可以推迟并在最初推迟。然后插入数据，然后启用FK约束。（实际上，让FK约束可以推迟是个好主意，因为将来你可能会遇到类似的问题，你会欣赏它的灵活性。）

不过，最好的解决方案是创建包含所有约束的表，就像您所做的那样。（请让FK约束可以推迟）。然后插入到DEPARTMENTS中，但保留所有CHAIRIDNULL。然后插入EMPLOYeE行，最后更新DEPARTMENTS行，以显示每个部门的适当主席。

关键是，FK列确实允许NULL——利用这一点打破恶性循环（循环逻辑）。

现在-你的要求是每个部门应该有一个椅子。。。您可以使列CHAIRID不可为空。这将再次阻碍我所描述的计划。仅在INSERT和UPDATE语句之后添加该约束（当然还有触发器）。