无法删除/更新Employee表数据：外键约束冲突问题求助

问题描述 #

执行Employee表的删除和更新操作时，触发外键约束错误：

Cannot delete or update a parent row: a foreign key constraint fails (bookmanagementsystem.orders, CONSTRAINT FK_Orders_Employee FOREIGN KEY (emp_id) REFERENCES Employee (emp_id))

涉及的操作语句：

DELETE FROM Employee WHERE emp_id = 103;
DELETE FROM Employee WHERE emp_id = 105;

UPDATE Employee SET emp_role = 'Sales Coordinator' WHERE emp_id = 101;
UPDATE Employee SET emp_role = 'Admin' WHERE emp_id = 104;
UPDATE Employee SET emp_role = 'Inventory Manager' WHERE emp_id = 102;

重现环境SQL #

CREATE DATABASE BOOKMANAGEMENTSYSTEM;

USE BOOKMANAGEMENTSYSTEM;

CREATE TABLE Employee (
emp_id INT PRIMARY KEY,
emp_name VARCHAR(20),
emp_role VARCHAR(30)
);

CREATE TABLE Customer (
cust_id INT PRIMARY KEY,
cust_name VARCHAR(20),
cust_add VARCHAR(30)
);

CREATE TABLE Supplier (
supp_id INT PRIMARY KEY,
supp_name VARCHAR(20),
supp_contact VARCHAR(40)
);

CREATE TABLE Book (
book_id INT,
book_price DECIMAL(10, 2),
genre VARCHAR(25),
book_name VARCHAR(20),  
PRIMARY KEY (book_id)
);

CREATE TABLE Inventory (
inventory_id INT,
category VARCHAR(20),
selling_price DECIMAL(10, 2),
PRIMARY KEY (inventory_id)
);

CREATE TABLE Invoice (
invoice_id INT,
supp_id INT,
book_id INT,
inventory_id INT,
PRIMARY KEY (invoice_id)
);

CREATE TABLE Payment (
pay_id INT,
item_qty INT,
Tprice DECIMAL (10,2),
order_id INT,
PRIMARY KEY (pay_id)
);

CREATE TABLE Orders (
order_id INT,
order_qty INT,
date DATE,
inventory_id INT,
book_id INT,
cust_id INT,
emp_id INT,
PRIMARY KEY (order_id)
);

ALTER TABLE Invoice
ADD CONSTRAINT FK_Invoice_Book FOREIGN KEY (book_id) REFERENCES Book (book_id),
ADD CONSTRAINT FK_Invoice_Supplier FOREIGN KEY (supp_id) REFERENCES Supplier (supp_id),
ADD CONSTRAINT FK_Invoice_Inventory FOREIGN KEY (inventory_id) REFERENCES Inventory (inventory_id);

ALTER TABLE Payment  
ADD CONSTRAINT FK_Payment_Orders FOREIGN KEY (order_id)  
REFERENCES Orders (order_id);

ALTER TABLE Orders
ADD CONSTRAINT FK_Orders_Inventory FOREIGN KEY (inventory_id) REFERENCES Inventory (inventory_id),
ADD CONSTRAINT FK_Orders_Book FOREIGN KEY (book_id) REFERENCES Book (book_id),
ADD CONSTRAINT FK_Orders_Customer FOREIGN KEY (cust_id) REFERENCES Customer (cust_id),
ADD CONSTRAINT FK_Orders_Employee FOREIGN KEY (emp_id) REFERENCES Employee (emp_id);

INSERT INTO Employee (emp_id, emp_name, emp_role)
VALUES ('101', 'Amirul', 'Cashier'),
       ('102', 'Nurin', 'Store Manager'),
       ('103', 'Diana', 'Admin'),
       ('104', 'Faris', 'Inventory Manager'),
       ('105', 'Christy', 'Sales Coordinator');

INSERT INTO Customer (cust_id, cust_name, cust_add)
VALUES ('201', 'Nuria', 'Taman Mawar, 88400'),
       ('202', 'Stephan', 'Taman Lily, 88400'),
       ('203', 'Khairul', 'Taman Kemboja, 88400'),
       ('204', 'Maria', 'Taman Tulip, 88400'),
       ('205', 'Rina', 'Taman Bunga Raya, 88400');

INSERT INTO Supplier (supp_id, supp_name, supp_contact)
VALUES ('301', 'Scholastic', 'scholastic@book.com'),
       ('302', 'Karya Seni', 'karyaseni@book.com'),
       ('303', 'Simon & Schuster', 'simonschuster@book.com'),
       ('304', 'Bloomsbury', 'bloomsbury@book.com'),
       ('305', 'HarperVia', 'harpervia@book.com');

INSERT INTO Book (book_id, book_price, genre, book_name)
VALUES ('401', '25.50', 'young adult', 'Thea Stilton'),
       ('402', '20.20', 'drama', 'Twisted fate'),
       ('403', '32.70', 'comedy', 'Dork diaries'),
       ('404', '45.60', 'fantasy', 'Harry Potter'),
       ('405', '27.00', 'non-fiction', 'Almond');

INSERT INTO Inventory (inventory_id, category, selling_price)
VALUES ('501', 'young adult fiction', '28.50'),     
       ('502', 'adult fiction', '25.20'),
       ('503', 'young adult fiction', '35.70'),
       ('504', 'adult fiction', '50.60'),
       ('505', 'adult non-fiction', '30.00');

INSERT INTO Invoice (invoice_id, supp_id, book_id, inventory_id)
VALUES ('00001', '301', '401', '501'),
       ('00002', '302', '402', '502'),
       ('00003', '303', '403', '503'),
       ('00004', '304', '404', '504'),
       ('00005', '305', '405', '505');

INSERT INTO Orders (order_id, order_qty, date, inventory_id, book_id, cust_id, emp_id)
VALUES ('10001', '1', '2024-01-11', '501', '401', '201', '101'),
       ('10002', '2', '2024-01-12', '502', '402', '202', '102'),
       ('10003', '1', '2024-01-13', '503', '403', '203', '103'),
       ('10004', '2', '2024-01-14', '504', '404', '204', '104'),
       ('10005', '1', '2024-01-15', '505', '405', '205', '105');

INSERT INTO Payment (pay_id, item_qty, Tprice, order_id)
VALUES ('20001', '1', '28.50', '10001'),
       ('20002', '2', '50.40', '10002'),
       ('20003', '1', '35.70', '10003'),
       ('20004', '2', '101.20', '10004'),
       ('20005', '1', '30.00', '10005');

解决方案 #

1. 处理删除操作 #

错误核心是Orders表存在关联emp_id=103和emp_id=105的记录，外键约束禁止删除父表（Employee）的关联行，有两种解决方式：

方式一：手动删除关联订单

先清理Orders表中对应员工的记录，再删除员工：

-- 删除关联的订单记录
DELETE FROM Orders WHERE emp_id IN (103, 105);
-- 删除目标员工记录
DELETE FROM Employee WHERE emp_id IN (103, 105);

方式二：配置级联删除（永久生效）

修改外键约束，让删除Employee记录时自动删除关联的Orders记录：

-- 删除原有外键约束
ALTER TABLE Orders DROP FOREIGN KEY FK_Orders_Employee;
-- 添加带级联删除的外键
ALTER TABLE Orders ADD CONSTRAINT FK_Orders_Employee 
FOREIGN KEY (emp_id) REFERENCES Employee(emp_id) 
ON DELETE CASCADE;

之后执行DELETE FROM Employee WHERE emp_id IN (103, 105);会自动清理关联的Orders记录。

2. 处理更新操作 #

更新emp_role字段本身不会触发外键约束（外键关联的是emp_id），如果更新也报错，大概率是之前的删除操作失败后事务未回滚导致的锁或事务状态异常。执行以下步骤：

-- 回滚未提交的事务
ROLLBACK;
-- 执行更新操作
UPDATE Employee SET emp_role = 'Sales Coordinator' WHERE emp_id = 101;
UPDATE Employee SET emp_role = 'Admin' WHERE emp_id = 104;
UPDATE Employee SET emp_role = 'Inventory Manager' WHERE emp_id = 102;
-- 提交更新
COMMIT;

3. 验证操作结果 #

执行完后可以查询验证：

-- 查看Employee表状态
SELECT * FROM Employee;
-- 查看Orders表关联记录是否已清理（若用级联删除）
SELECT * FROM Orders;

内容的提问来源于stack exchange，提问作者user23204223