mysql delete element when it has no link with others

Question

I have a database with 3 tables:

• Users
• Users_cards
• Cards

The table users_cards has two columns:

• user_id (is foreign key of Users.id)
• card_id (is foreign key of Cards.id)

users and cards tables have its own id as primary key, and the two columns of users_cards are foreign keys of them, with cascade option on delete, so when I delete one card from cards, it is also deleted from users_cards, and the same as with users.

But also, I want that when I delete one row of users_cards table, if that card has no more users, this is, if that row is the only row where that card_id appears in the table, it must be deleted also from cards.

What is the best way to achieve this? Can it be done "automatically" or do I have to check everytime I delete one row from users_cards this?

Answer 1

Can it be done "automatically" or do I have to check every time I delete one row from users_cards this?

Depends what you mean on automatically. I would use triggers:

DELIMITER $$

CREATE DEFINER=CURRENT_USER TRIGGER `name_of_this_trigger`
AFTER DELETE ON `users_cards` FOR EACH ROW
BEGIN

    DELETE FROM `cards` WHERE `id` NOT IN (SELECT DISTINCT `card_id` FROM `users_cards`);

END $$

DELIMITER ;

If you execute it on your database, it will add a trigger which runs and checks for user-less cards, and delete them without any additional hassle after each DELETE query on the users_cards table. However, it will not run, thus failing to delete any potentially user-less cards, if a users_cards has been deleted by a cascade event. So, you can do two things. Nothing: the garbage will be cleaned up on the next DELETE query on users_cards anyhow. Or; You can add another trigger, which runs after a DELETE query on the users table like so (to delete any potential cards which now doesn't have a users_cards due to a cascading DELETE originating from users):

DELIMITER $$

CREATE DEFINER=CURRENT_USER TRIGGER `name_this_very_trigger`
AFTER DELETE ON `users` FOR EACH ROW
BEGIN

    DELETE FROM `cards` WHERE `id` NOT IN (SELECT DISTINCT `card_id` FROM `users_cards`);

END $$

DELIMITER ;

FYI: If you noticed, there is only one statement after the trigger declaration, so you don't even need the BEGIN ... END block, which means that the DELIMITER nonsense is needless as well.

Let me know if it helped!

ps.: And for God's sake; Please don't use plural table names. You are naming the entities stored in the table not the table itself.

Answer 2

Please try the following...

CASE ( SELECT COUNT( * )
       FROM users_cards
       WHERE card_id = targetID )
    WHEN 0 THEN
        DELETE
        FROM Cards
        WHERE id = targetID;
    WHEN 1 THEN
        DELETE Cards,
               users_cards
        FROM cards
        JOIN users_cards
        WHERE Cards.id = users_cards.card_id
          AND Cards.id = targetID;
    ELSE
        *** Delete a user_card ***
END

This statement will count the number of entries remaining in users_cards where card_id equals the target ID.

If there are none, it has detected a Card with no corresponding Users, which it then deletes.

If there is one then it deletes the entries from Cards and users_cards corresponding to the target ID.

If there is more than one then please use your existing DELETE statement for that scenario.

If you have any questions or comments, then please feel free to post a Comment accordingly.

Further Reading

Mysql - delete from multiple tables with one query (Pekka's Answer)

https://dev.mysql.com/doc/refman/5.7/en/delete.html (for single- and multiple-table DELETE syntax)

https://dev.mysql.com/doc/refman/5.7/en/case.html ( for CASE)

Answer 3

You have to set the property at design time table what action should be performed on child table when the row from master table is be deleted.

ON DELETE CASCADE will delete the corresponding row from child table too. ON UPDATE CASCADE will update the reference key value corresponding row in child table too.

CREATE TABLE categories (
id int unsigned not null primary key,
name VARCHAR(255) default null
)Engine=InnoDB;

CREATE TABLE products (
id int unsigned not null primary key,
name VARCHAR(255) default null
)Engine=InnoDB;

CREATE TABLE categories_products (
category_id int unsigned not null,
product_id int unsigned not null,
PRIMARY KEY (category_id, product_id),
KEY pkey (product_id),
FOREIGN KEY (category_id) REFERENCES categories (id)
   ON DELETE CASCADE
   ON UPDATE CASCADE,
FOREIGN KEY (product_id) REFERENCES products (id)
   ON DELETE CASCADE
   ON UPDATE CASCADE
)Engine=InnoDB;