mysql - 如何设计表，以便删除相关表上的查询只会删除它们之间的关系？

Question

我有 teams 和 member 表，它们由 team_member 表连接。 团队和成员具有多对多关系。当我删除团队时，我需要从 team_member 中删除所有关系对(团队 - 成员)并从 teams 中删除相应记录。我无法在 team_member 表中设置ON DELETE CASCADE，因为它将删除成员(可以与许多团队相关)。删除成员也采用相同的策略。最好的设计方法是什么？

当前设计:

CREATE TABLE `member` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(100) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8;

CREATE TABLE `organization` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(45) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8;

CREATE TABLE `teams` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(45) NOT NULL,
  `rating` int(2) NOT NULL,
  `FK_Organization_id` int(11) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `FK_Organization_id` (`FK_Organization_id`),
  CONSTRAINT `FK_Organization_id` FOREIGN KEY (`FK_Organization_id`) REFERENCES `organization` (`id`)
  ON CASCADE NO ACTION ON UPDATE NO ACTION
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8;

CREATE TABLE `team_member` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `FK_Member_id` int(11) NOT NULL,
  `FK_Team_id` int(11) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `FK_Member_id` (`FK_Member_id`),
  KEY `FK_Team_id` (`FK_Team_id`),
  CONSTRAINT `FK_Member_id` FOREIGN KEY (`FK_Member_id`) REFERENCES `member` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION,
  CONSTRAINT `FK_Team_id` FOREIGN KEY (`FK_Team_id`) REFERENCES `teams` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION
) ENGINE=InnoDB AUTO_INCREMENT=13 DEFAULT CHARSET=utf8;

Answer 1

I cannot set in team_member table ON DELETE CASCADE because it will delete member

ON DELETE CASCADE 不会那样工作。以下两个外键约束将

• 删除团队时级联删除到 team_member，并且
• 当删除成员时，级联会删除到 team_member。

在任何情况下，删除团队都不会同时删除成员中的行，并且在任何情况下删除成员都不会同时删除团队中的行。

CONSTRAINT `FK_Member_id` FOREIGN KEY (`FK_Member_id`) 
  REFERENCES `member` (`id`) ON DELETE CASCADE ON UPDATE NO ACTION,
CONSTRAINT `FK_Team_id` FOREIGN KEY (`FK_Team_id`) 
  REFERENCES `teams` (`id`) ON DELETE CASCADE ON UPDATE NO ACTION

构建表格，添加一些行，然后自行测试。