多对多表中的级联删除

yrwegjxp  于 2021-06-23  发布在  Mysql
关注(0)|答案(1)|浏览(421)

假设一组邮件列表,其中个人可能是多个列表的成员。我设置了一个联接表:

members -> members2lists <- lists

如果用户只想从一个列表中删除一个成员,那么似乎只需要删除members2lists表中相应的行。但是,如何指定级联,以便在它们只是一个列表的成员时不将它们作为孤立的?换句话说,当且仅当某个成员是我要从中删除的列表中的成员时,如何删除该成员?
我在innodb表中使用php和mysql。

ymzxtsji

ymzxtsji1#

谢谢大卫的回复。我用这种方法解决了这个问题(我希望前两个函数是自我解释的):

$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();
}

我很高兴的意见,从成员对如何改善这一代码!

相关问题