Mysql在一条语句中删除多个具有左联接的表,导致FK冲突。例如,
Student StudentDetail
-------- ------------------------------
id, name studentId, birthday, address
100 John 100 1/1/1982 A1
200 Scott 200 1/1/1978 A2
300 Tiger 300 1/1/1988 A3
FullTimeStudent
------------------
studentId, program
200 P1
StudentDetail和FullTimeStudent的studentId列是引用Student表的id列的FK。
删除id为200 的学生
delete t0, t2, t1 from StudentDetail t1
LEFT join FullTimeStudent t2 on (t1.studentId=t2.studentId)
LEFT join Student t0 on (t1.studentId=t0.id) where t0.id=200;
错误:违反外键约束。
如果将左侧联接更改为内部联接,则删除操作成功。左联接有什么问题?
但左联接和内联接之间没有区别。
select t0.id, t2.studentId, t1.studentId from StudentDetail t1
LEFT join FullTimeStudent t2 on (t1.studentId=t2.studentId)
LEFT join Student t0 on (t1.studentId=t0.id) where t0.id=100;
返回一行。
来自手册:
如果使用涉及InnoDB表的多表DELETE语句对于存在外键约束的,MySQL优化器可能以与表不同的顺序处理表父母/子女关系。在这种情况下,语句将失败并滚动返回相反,您应该从单个表中删除,并依赖InnoDB提供的ON DELETE功能可导致其他表以进行相应的修改。
正如手册(和@Barmar(所说,解决这一问题的方法是将ON DELETE CASCADE
添加到您的约束中,例如在FullTimeStudent
和StudentDetail
中使用:
FOREIGN KEY (studentID) REFERENCES Student(id) ON DELETE CASCADE
您按错误的顺序执行LEFT JOIN
。第一个表应该是master表,然后您应该连接到detail表。
DELETE t0, t1, t2
FROM Student t0
LEFT JOIN FullTimeStudent t1 ON t1.studentID = t0.id
LEFT JOIN StudentDetail t2 ON t2.studentID = t0.id
WHERE t0.id = 200
但是,正确的设计是在外键约束中使用ON DELETE CASCADE
选项。然后你只需要从主表中删除,相关的行就会自动删除。
DELETE FROM Student
WHERE id = 200