How to use DELETE ON CASCADE in relation to each other

Question

Can someone help me. I tried something, but I'm new to (my) SQL too.

I use two tables: Elements and categories. Table elements have a field with a foreign key: category_id.

I want category tables to be maintained neatly. Therefore, when not a single item in the Element belongs to category X in categories, category X should be removed from the categories. How do you install it. I guessed using DELETE ON CASCADE, but so far it only removed elements from elements when I removed a category from categories.

Thanks for helping me!

Answer 1

ON DELETE CASCADE is a way to delete a row when a row is deleted. It means:

• You have a row in table A
• You have a row in table B that refers to a row in table A
• You delete the row in table A
• The database deletes the corresponding row in table B

So, you have elements, and each element belongs to a certain category. In the table of your products, you have a category_id (and please correct the spelling), which refers to a row in the category table. So, in your situation:

• You have a category
• You have an item that refers to a category
• You delete category
• All items corresponding to this category are deleted in the database.

What you are asking for is similar to another:

• You have items
• You delete the last item in a specific category
• The database goes and finds this category and deletes it

There is no way to do this with ON DELETE CASCADE for two reasons:

• How are you going to create an empty category before you insert your first element into it? The database will need to delete it immediately.
• The database will have to do additional work to scan the table. He does not “know” that element No. 23082 was the last element in the category; he will have to somehow track the number of items in the category to do this.

All this is due to the fact that ON DELETE CASCADE is a way to maintain referential integrity. That is, this is a way for the database to give you a strong guarantee that if you see category # 20393 for item # 9847, when you look for category # 20393, you know that it exists. This is not a labor-saving device. :) This is why the other ON DELETE SET NULL and ON DELETE RESTRICT parameters: they also guarantee integrity, but instead of deleting, they remove a bad link or prevent the initial removal from appearing.

So the answer is: you will either have to write a cron job to periodically clean this table, or use some ON DELETE trigger if you are concerned about empty categories.