mysql - 在尝试删除 MySQL 中的任何这些记录之前，如何检查记录列表的外键引用？

Question

当您有一个记录列表时，有没有办法在您尝试删除任何这些记录之前检查这些记录中的每一个是否都有外键引用？

举个例子，如果我有一个借书人列表和一个图书列表，如果借书人还有借书，你就不能从系统中删除他。 (然而，我的实际系统比那复杂得多——表格多得多。)

我想删除所有借书人的删除选项(在那个例子中)。

如果我尝试删除带有外键引用的记录，我会收到错误消息:

Database access failed: Cannot delete or update a parent row: a foreign key constraint fails (dbname.tablename, CONSTRAINT fkfieldid FOREIGN KEY (fieldid) REFERENCES tablename (fieldid))

一个解决方案是编写一个查询来检查记录列表中的每条记录是否在它可能被引用的任何可能表中具有任何外键引用。

但是，如果我希望在我的内容管理系统中显示一个表中的 100 条记录的列表，并且我必须运行 100 个子查询才能显示该列表，这显然是非常低效的!

最终用户在尝试删除记录时会感到困惑，但他们不能这样做，因为该数据在其他地方“正在使用”，所以我宁愿删除删除选项以避免混淆。

关于什么是最好的事情有什么想法吗？谢谢。

Answer 1

您可以使用子查询或连接轻松地做到这一点。

即。子查询

SELECT B.*, (SELECT COUNT(*) FROM loans L WHERE L.book_id = b.id) loan_count
FROM books B

或者加入(注意如果允许同时多借，这样的书会在结果中出现不止一次):

SELECT B.*, L.book_id AS loaned_out_if_not_null
FROM books B
    LEFT JOIN loans L ON B.id = L.book_id

这当然可以用 GROUP BY 来缩短:

SELECT B.id, B.name, COUNT(L.book_id) AS loan_count
FROM books B
    LEFT JOIN loans L ON B.id = L.book_id
GROUP BY B.id, B.name

如果你的数据库引擎支持 CASE，你可以通过结合 DISTINCT 来避免结果中的多行(当然，DISTINCT 也有开销):

SELECT DISTINCT B.*,
    CASE WHEN L.book_id IS NOT NULL THEN 1 ELSE 0 END AS loaned_out_if_one
FROM books B
    LEFT JOIN loans L ON B.id = L.book_id

其中，我会选择 GROUP BY 变体。