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

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 ale开发者_如何学JAVArt 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.*

---

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.

---

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?

---

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.

---

I think you can do that with a trigger.