如何在SQL中删除条件父子关系

2. DELETE FROM B WHERE NOT EXISTS(SELECT * FROM C where idB = B.id)
3. DELETE FROM A WHERE NOT EXISTS(SELECT * FROM B where idA = A.id)

通过这种方式,您可以在执行删除之前检查是否会违反外键。对于大型表,您应该考虑添加索引。

我通常避免使用它们,因为它们在过度使用时会导致性能的噩梦,但这听起来几乎就是触发器的确切用例,假设您总是希望这样做。

下面是另一篇文章中的一个例子: phpMyAdmin create a trigger for deleting a parent row when no child rows exist .

SELECT Employees.ID, COUNT(Orders.OrderID) AS NumberOfOrders
FROM (Employees
LEFT JOIN Orders ON Orders.EmployeeID = Employees.EmployeeID)
GROUP BY Employees.ID
HAVING COUNT(Orders.OrderID) = 0;

其中员工=A,订单=B