mysql - 如何删除表中的记录，该表中的主键在另一个表中作为外键引用？

Question

在我的数据库中，我有一个表说“employee”，它有一个主键属性“employee_id”和另一个属性“salary”。还有另一个表说“employee_qualification”，它有一个外键属性“employee_id”。那么，如何删除“employee”和“employee_qualifications”中薪水=10000 的所有记录？

这似乎不起作用。

DELETE FROM employee WHERE employee.salary=10000;

标签： mysqlsqldatabase

Answer 1

您可以执行两条语句。

DELETE FROM employee_qualifications WHERE employee_qualifications.employee_id in (SELECT employee_id FROM employee WHERE employee.salary=10000)
DELETE FROM employee WHERE employee.salary=10000

虽然这会起作用，但它可能会在employee_qualifications 表中留下一些孤立的记录。以下选项将清除孤立的记录。

DELETE FROM employee WHERE employee.salary=10000
DELETE FROM employee_qualifications WHERE employee_qualifications.employee_id Not in (SELECT employee_id FROM employee)