mysql - 如何在mysql中删除而不删除主要和外部约束

例如，我想删除员工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

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

最佳答案

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

UPDATE Department SET manager_id = ? WHERE manager_id = ?;

同时更新Employee中引用要作为其主管id删除的行的所有行。

UPDATE Employee SET supervisor_id = ? WHERE supervisor_id = ?;

一旦部门或员工中没有引用该特定员工id的行，则可以删除员工表中的行。

关于mysql - 如何在mysql中删除而不删除主要和外部约束，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/58687071/