mysql - 多对多关系:如何从 3 个表中删除记录?
问题描述
例如,我有以下表格:
person person_address address
========== ================== ===========
pid (PK) pid (FK) aid (PK)
... aid (FK) ...
我已经设置pid
为ON DELETE CASCADE
和aid
。ON DELETE NO ACTION
目标是在删除address
aperson
时删除相应的记录。
我试过触发器:
CREATE TRIGGER pa_delete
AFTER DELETE ON person_address
FOR EACH ROW
DELETE FROM address
WHERE person_address.address_id = address.address_id
但是当我删除person
记录时,address
记录仍然存在。我究竟做错了什么?
编辑
涉及的表:
CREATE TABLE IF NOT EXISTS `chkdcrm`.`person` (
`person_id` INT(11) NOT NULL AUTO_INCREMENT,
`first_name` VARCHAR(45) NOT NULL,
`last_name` VARCHAR(45) NOT NULL,
`tel` VARCHAR(20) NULL,
`fax` VARCHAR(20) NULL,
`mobile` VARCHAR(20) NOT NULL UNIQUE,
`email` VARCHAR(150) NOT NULL UNIQUE,
PRIMARY KEY (`person_id`))
ENGINE = InnoDB;
CREATE TABLE IF NOT EXISTS `chkdcrm`.`address` (
`address_id` INT(11) NOT NULL AUTO_INCREMENT,
`type` ENUM('Invoice', 'Shipping', 'Site', 'Mailing') NOT NULL,
`street` VARCHAR(100) NOT NULL,
`city` VARCHAR(100) NOT NULL,
`state` VARCHAR(30) NOT NULL,
`zip` VARCHAR(10) NOT NULL,
`country` VARCHAR(40) NOT NULL,
PRIMARY KEY (`address_id`))
ENGINE = InnoDB;
CREATE TABLE IF NOT EXISTS `chkdcrm`.`person_address` (
`address_id` INT(11) NOT NULL,
`person_id` INT(11) NOT NULL,
PRIMARY KEY (`address_id`, `person_id`),
CONSTRAINT `fk_pa_address`
FOREIGN KEY (`address_id`)
REFERENCES `chkdcrm`.`address` (`address_id`)
ON DELETE NO ACTION,
CONSTRAINT `fk_pa_person`
FOREIGN KEY (`person_id`)
REFERENCES `chkdcrm`.`person` (`person_id`)
ON DELETE CASCADE)
ENGINE = InnoDB;
解决方案
推荐阅读
- firebase - Firebase 实时数据库,删除重复记录
- node.js - webpack-cli 未知参数:--output
- excel - 如何计算某个单元格范围内有多少个整数
- javascript - 原型中的 Setter 属性与数据属性
- xcode - 如何在 Big Sur 上删除 NSTableView 缩进
- java - 执行器不适用于 Spring Boot 反应式 weblux
- javascript - 对 WordPress 插件的 Ajax 调用不起作用
- windows - 根据名称复制文件夹和内容,没有完整路径
- javascript - 每次调用方法时变量都是未定义的
- python - python - imagegrab 模块在使用 bbox 时返回错误