delete rows from a table when a specific row from another table has been deleted

Question

I have Group and each group has contacts associated with it. When a user deletes a group, if the group is not empty then it will alert them that all contacts in that group will be deleted if they continue. Well anyways, so my problem is setting up that feature.

I have tried to figure out how I can delete all contacts that belong to that group and delete the group as well.

Before I continue I'm wondering but is there a sorta of automated way of doing this via foreign keys?

Well if not its ok, this is my query but SQL Workbench is throwing out the following error

DELETE c
FROM `list_`.`contacts` AS c
INNER JOIN `list_`.`groups` AS g ON c.group_id = g.id
WHERE g.group = 'School' 
  AND c.user_id = 2;

error:

Error Code: 1046 No database selected

really confused here, also I have also tried c.*

Answer 1

MySQL supports multi-table deletions in a single statement - use:

DELETE c, g
  FROM `list_`.`contacts` AS c
  JOIN `list_`.`groups` AS g ON c.group_id = g.id
                            AND g.group = 'School' 
 WHERE c.user_id = 2;

Regarding error code 1046, when using WorkBench make sure the appropriate database/catalog is selected in the drop down menu found above the Object Browser tab. You can specify the default schema/database/catalog for the connection - click the "Manage Connections" options under the SQL Development heading of the Workbench splash screen.

Answer 2

I think you can do that with a trigger.

Answer 3

You don't state your RDBMS, but in SQL Server you could turn on cascade deletes, BUT I wouldn't advise doing that; it's too dangerous.

Update: MySQL InnoDB supports cascading deletes as well: FOREIGN KEY Constraints

It is safer to first manually delete all the referencing rows, and then delete the group.

The error message "Error Code: 1046 No database selected" suggests that it is NOT your TSQL at fault. Are you pointing to a database?

Answer 4

In Mysql you can try something like this for the trigger:

DELIMITER $$
DROP TRIGGER IF EXISTS `deluser`$$

CREATE TRIGGER `deluser` BEFORE DELETE on `biguser`
FOR EACH ROW
BEGIN
DELETE FROM smalluser WHERE id=OLD.id;
END$$

DELIMITER ;

Note: the trigger has to be before delete otherwise you might lose the key you want to use to delete the records.