mysql - 如何在不删除主要和外部约束的情况下在 mysql 中删除

Question

例如，我想删除 employee_id 中的一条记录

DELETE FROM EMPLOYEE WHERE employee_id = 114;

CREATE TABLE EMPLOYEE(
employee_id    DECIMAL(6)     NOT NULL,
first_name     VARCHAR(20)  NOT NULL,
last_name      VARCHAR(25)  NOT NULL,
email          VARCHAR(25)      NULL,
phone_number   VARCHAR(20)      NULL,
hire_date      DATE     NOT NULL,
job_title      VARCHAR(35)  NOT NULL,
salary         DECIMAL(8,2)     NULL,
commission_pct DECIMAL(2,2)     NULL,
supervisor_id     DECIMAL(6)        NULL,
department_name VARCHAR(30)     NULL,
CONSTRAINT EMPLOYEE_PK PRIMARY KEY(employee_id),
CONSTRAINT EMPLOYEE_CK1 UNIQUE(email),
CONSTRAINT EMPLOYEE_CK2 UNIQUE(phone_number),
CONSTRAINT EMPLOYEE_FK1 FOREIGN KEY(department_name)
       REFERENCES DEPARTMENT(department_name),
CONSTRAINT EMPLOYEE_FK2 FOREIGN KEY(supervisor_id)
       REFERENCES EMPLOYEE(employee_id),
CONSTRAINT EMPLOYEE_FK3 FOREIGN KEY(job_title)
       REFERENCES JOB(job_title),
CONSTRAINT EMPLOYEE_CH1 CHECK (salary > 0) );

ALTER TABLE DEPARTMENT
ADD ( CONSTRAINT DEPARTMENT_FK2 FOREIGN KEY(manager_id)
             REFERENCES EMPLOYEE(employee_id) );

这是我收到的错误信息

ERROR 1217 (23000): Cannot delete or update a parent row: a foreign key constraint fails

知道如何在不改变或删除约束的情况下删除记录吗？

Answer 1

要在不删除外键的情况下删除员工记录，您应该首先更新 Department 表中引用该 employee_id 的所有行。

UPDATE Department SET manager_id = ? WHERE manager_id = ?;

同时更新 Employee 中所有引用您要删除的作为其 supervisor_id 的行。

UPDATE Employee SET supervisor_id = ? WHERE supervisor_id = ?;

一旦 Department 或 Employee 中没有引用该特定 employee_id 的行，您就可以删除 Employee 表中的行。