Cascade delete in many-to-many tables

Question

Assume a set of mailing lists in which individuals may be members of more than one list. I've set up a join table:

members -> members2lists <- lists

If the user wants to remove a member from one list only, it would seem necessary only to delete the appropriate row in the members2lists table. But how do I specify the cascading so as not to leave them as an orphan if they're a member of only one list? In other words, how do I delete a member if and only if they are a member of solely the list I'm removing them from?

I'm using PHP and mySQL with InnoDB tables.

Since `members` doesn't depend on `members2lists` I suspect cascading deletes won't accomplish this. Perhaps a trigger on `members2lists` which deletes from `members` any IDs not found in itself? — David, Aug 13 '18 at 09:55

score 0 · Answer 1 · answered Aug 13 '18 at 15:58

Thanks David for your response. I've solved the problem this way (I hope the first two functions are self-expanatory):

$memberID = getIDFromMembers($pdo, $email);
$currListID = getIDFromList($pdo, $listname);

// remove record from join table
$sql = 'DELETE FROM `members2lists` WHERE `member_fk` = :member AND `list_fk` = :list';
$stmt = $pdo->prepare($sql);
$stmt->bindParam(':member', $memberID);
$stmt->bindParam(':list', $currListID);
$stmt->execute();

// check if another record exists in the join table for the same member
$sql = 'SELECT `member_fk` FROM `members2lists` WHERE `member_fk` = :member';
$stmt = $pdo->prepare($sql);
$stmt->bindParam(':member', $memberID);
$stmt->execute();
$row = $stmt->fetch();

if($row === false) 
{
  // there isn't, the member is an orphan, so delete
  $sql = 'DELETE FROM `members` WHERE `member_email` = :email';
  $stmt = $pdo->prepare($sql);
  $stmt->bindParam(':email', $email);
  $stmt->execute();
}

I'd be glad of comments from members about ways to improve this code!

Cascade delete in many-to-many tables

1 Answers1