如何在多对一关系上使用 DELETE ON CASCADE
有人可以帮助我吗？我正在尝试一些东西，但我对（我的）SQL 太陌生了。
我使用两个表：项目和类别。表项有一个带外键的字段：category_id。
我希望表格类别保持整洁。因此，当 Items 中没有任何项目属于categories 中的类别 X 时，应从categories 中删除类别 X。你如何确定这一点。我猜测是通过使用 DELETE ON CASCADE 来实现的，但到目前为止，当我从类别中删除类别时，它只是从项目中删除相应的项目。
非常感谢您帮助我！
May someone help me out. I'm trying something, but I'm (too) new to (my)SQL.
I use two tables: Items and Categories. Table Items has a field with foreign key: category_id.
I want the table Categories to be kept tidy. So when no item in Items is of category X in Categories, the category X should be deleted from Categories. How do you establish that. I guessed by using DELETE ON CASCADE, but so far it was only deleting corresponding items from Items when I deleted a categorie from Categories.
Thanks a lot for helping me!
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（1）
ON DELETE CASCADE是一种在删除引用的行时删除该行的方法。这意味着：因此，您有项目，每个项目属于一个特定的类别。在您的项目表中，您有一个引用类别表中的行的category_id（请修正拼写）。因此，在您的情况下：
您所要求的有点相反：
无法使用
ON DELETE CASCADE执行此操作，原因有两个：这一切都源于这样一个事实：
ON DELETE CASCADE是一种维护引用完整性的方法。也就是说，数据库可以向您提供强有力的保证，即如果您在项目 #9847 上看到类别 #20393，那么当您查找类别 #20393 时您就知道它存在。它不是一种节省劳力的设备。 :) 这就是为什么其他选项是ON DELETE SET NULL和ON DELETE RESTRICT：它们也保证完整性，但不是删除，而是删除错误引用或防止原来的删除发生。所以答案是，如果您担心空类别，您将必须编写一个 cron 作业来定期清理该表，或者使用某种 ON DELETE 触发器。
ON DELETE CASCADEis a way of deleting a row when a row it references is deleted. This means:So you have items, and each item belongs to a particular category. In your items table, you have a category_id (and please fix your spelling) that refers to a row in the categories table. So, in your situation:
What you're asking for is sort of the other way around:
There's no way to do this with
ON DELETE CASCADE, for two reasons:This all stems from the fact that
ON DELETE CASCADEis a way of maintaining referential integrity. That is, it's a way for the database to give you a strong guarantee that if you see category #20393 on item #9847, when you go look for category #20393 you know it exists. It is not a labor saving device. :) This is why the other options areON DELETE SET NULLandON DELETE RESTRICT: they also guarantee integrity, but instead of deleting, they remove the bad reference or prevent the original delete from occurring.So the answer is, you will have to either write a cron job to periodically clean that table or use some kind of ON DELETE trigger, if you're worried about empty categories.