如何在多对一关系上使用DELETE ON CASCADE

May someone help me out. I'm trying something, but I'm (too) new to (my)SQL.

愿有人帮助我。我正在尝试一些东西，但我（也）是（我的）SQL的新手。

I use two tables: Items and Categories. Table Items has a field with foreign key: category_id.

我使用两个表：项目和类别。表项具有外键字段：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.

我希望表格类别保持整洁。因此，如果项目中的项目不属于类别中的类别X，则应从类别中删除类别X.你是如何建立的。我通过使用DELETE ON CASCADE猜测，但到目前为止，当我从类别中删除类别时，它只删除了项目中的相应项目。

Thanks a lot for helping me!

非常感谢帮助我！

1 个解决方案

#1

ON DELETE CASCADE is a way of deleting a row when a row it references is deleted. This means:

ON DELETE CASCADE是一种在删除引用的行时删除行的方法。意即：

You have a row in table A
表A中有一行
You have a row in table B that references a row in table A
表B中有一行引用表A中的行
You delete the row in table A
您删除表A中的行
The database deletes the corresponding row in table B
数据库删除表B中的相应行

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:

所以你有物品，每个物品都属于一个特定的类别。在您的项目表中，您有一个category_id（并且请修复您的拼写），它引用了类别表中的一行。所以，在你的情况下：

You have a category
你有一个类别
You have an item that references a category
您有一个引用类别的项目
You delete a category
您删除一个类别
The database deletes all the items that correspond to that category
数据库将删除与该类别对应的所有项目

What you're asking for is sort of the other way around:

你要求的是另一种方式：

You have items
你有物品
You delete the last item in a particular category
您删除特定类别中的最后一项
The database goes and finds that category and deletes it
数据库转到并找到该类别并删除它

There's no way to do this with ON DELETE CASCADE, for two reasons:

使用ON DELETE CASCADE无法做到这一点，原因有两个：

How are you going to create an empty category before inserting your first item into it? The database would have to delete it immediately.
在将第一个项目插入其中之前，您将如何创建一个空类别？数据库必须立即删除它。
The database would have to do a lot of extra work scanning the table. It doesn't "know" that item #23082 was the last item in the category; it would have to somehow be keeping track of the number of items in the category to do that.
数据库必须在扫描表格时做很多额外的工作。它不“知道”项目＃23082是该类别中的最后一项;它必须以某种方式跟踪该类别中的项目数量。

This all stems from the fact that ON DELETE CASCADE is 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 are ON DELETE SET NULL and ON DELETE RESTRICT: they also guarantee integrity, but instead of deleting, they remove the bad reference or prevent the original delete from occurring.

这一切都源于ON DELETE CASCADE是一种维护参照完整性的方式。也就是说，这是数据库为您提供强有力保证的一种方式，如果您在项目＃9847上看到类别＃20393，当您去寻找类别＃20393时，您知道它存在。它不是省力的设备。 :)这就是为什么其他选项是ON DELETE SET NULL和ON DELETE RESTRICT：它们也保证完整性，但它们不是删除，而是删除错误引用或阻止原始删除发生。

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.

所以答案是，如果您担心空类别，则必须编写一个cron作业来定期清理该表或使用某种ON DELETE触发器。

#1