在尝试删除 MySQL 中的任何记录之前,如何检查记录列表的外键引用?
当您有记录列表时,有没有办法在尝试删除任何这些记录之前检查这些记录中的每一个是否都有外键引用?
举个例子,如果我有一个借阅者列表和一个图书列表,如果借阅者仍然有借阅的图书,那么您不应该能够从系统中删除他。 (然而,我的实际系统比这复杂得多 - 有更多的表。)
我想从任何拥有借阅书籍的借款人中删除删除选项(在该示例中)。
如果我尝试删除带有外键引用的记录,则会收到以下错误:
数据库访问失败:无法删除或更新父行:外键约束失败 (
dbname
.tablename
, CONSTRAINTfkfieldid
FOREIGN KEY (fieldid
) 参考表名
(fieldid
))
一种解决方案是编写一个查询来检查记录列表中的每条记录是否具有外键可以引用任何可能的表中的引用。
但是,如果我希望显示内容管理系统中表中 100 条记录的列表,并且必须运行 100 个子查询才能显示该列表,这显然是非常低效的!
当最终用户尝试删除记录但又不能删除记录时,他们会感到困惑,因为该数据在其他地方“正在使用”,所以我宁愿删除删除选项以避免混淆。
关于什么是最好的事情有什么想法吗? 谢谢。
Is there a way, when you have a list of records, to check if each of these records have foreign key references before you attempt to delete any of these records?
As an example, if I have a list of borrowers and a list of books, you should not be able to delete a borrower from the system if he still has books on loan. (My actual system is much more complicated than that however - a lot more tables.)
I would like to remove the delete option from any borrowers that have books on loan (in that example).
If I try to delete a record with foreign key references, I get an error to the effect of:
Database access failed: Cannot delete or update a parent row: a foreign key constraint fails (
dbname
.tablename
, CONSTRAINTfkfieldid
FOREIGN KEY (fieldid
) REFERENCEStablename
(fieldid
))
One solution is to write a query to check if each record, in a list of records, has any foreign key references in any of the possible tables it could be referenced.
However, if I wish to display a list of 100 records from a table in my content management system, and I have to run 100 sub-queries in order to display that list, it is obviously very inefficient!
End users become confused when they try to delete a record but they can't because that data is 'in use' elsewhere, so I would rather remove the option to delete to avoid confusion.
Any ideas on what would be the best thing to do?
Thanks.
如果你对这篇内容有疑问,欢迎到本站社区发帖提问 参与讨论,获取更多帮助,或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱,如果其他用户或者作者回复了您的评论,将不能在第一时间通知您!
发布评论
评论(2)
您可以使用子查询或联接轻松地完成此操作。
IE。子查询
或连接(请注意,如果您允许多个同时借阅,这样的书将在结果中出现多次):
这当然可以使用 GROUP BY 来缩短:
如果您的数据库引擎支持 CASE,那么您可以通过将其与 DISTINCT 组合来避免结果中的多行(当然,DISTINCT 也有开销):
其中,我会使用 GROUP BY 变体。
You can easily do this using either a sub-query or a join.
ie. sub-query
or join (note that if you allow multiple simultaneous loans, such a book will appear more than once in the results):
This can of course be shortened down with a GROUP BY:
If your database engine has support for CASE, you can avoid the multiple rows in the result by combining that with DISTINCT (of course, DISTINCT also has overhead):
Of these, I would go with the GROUP BY variant.
您可能喜欢的是交易。
1) 删除所有条目。
2a) 如果发生任何错误->回滚
2b) 如果没有发生错误->提交
http://dev.mysql.com/doc/refman/5.0 /en/commit.html
What you maybe like are transactions.
1) Delete all entries.
2a) If any error occured -> Rollback
2b) If no error occured -> Commit
http://dev.mysql.com/doc/refman/5.0/en/commit.html