我现在的处境是:
背景
我有两个数据库模式称为“prod”和“stg”。
“prod”包含两个名为“parent”和“child”的表
“stg”只有“parent”表
“parent”表定义在“prod”和“stg”模式中是相同的。
在删除记录的情况下,“父”表定义为软删除(逻辑删除,即设置delete_flg为“1”),而“子”表定义为真删除(物理删除记录)
目标
我正在努力实现以下目标:
当且仅当“prod”、“parent”和“stg”、“parent”都被删除时(无论是物理上还是逻辑上,或在一侧不存在),然后自动将删除操作(物理上删除)级联到“prod”、“child”表中“sp_id”与“parent”中的值匹配的记录。
例如,假设我
"prod"."parent"
+----+---------+--------+
| SP_ID | SP_NAME | DELETE_FLG |
+----+---------+--------+
| 1 | 1 | 1 |
+----+---------+--------+
"prod"."parent"
+----+---------+--------+
| SP_ID | SP_NAME | DELETE_FLG |
+----+---------+--------+
| 1 | 1 | 1 |
+----+---------+--------+
"stg"."parent"
+----+---------+--------+
| SP_ID | SP_NAME | DELETE_FLG |
+----+---------+--------+
| 1 | 1 | 0 |
+----+---------+--------+
"prod"."child"
+----+---------+
| SP_ID | JOB_KEY |
+----+---------+
| 1 | key |
+----+---------+
,如果我执行一个sql更新“stg”。“parent”将delete_flg=1设置为sp_id=1,这将在逻辑上删除“parent”表中sp_id为1的最后一个“existing”记录,那么“prod”中的记录“.child”也将被mysql自动物理删除。
问题
我一直在考虑让子表中的sp_id作为外键引用父表中的sp_id(
https://dev.mysql.com/doc/refman/8.0/en/create-table-foreign-keys.html
)
然而,
a)我不知道是否可以在不同的模式中引用多个表,以及
b)MySQL似乎只支持级联相同的操作,即先删除后删除子项,或先更新后更新子项。但在我的例子中,我想要一个关于父母的更新然后删除孩子。
有人能帮帮我吗?
这在mysql中可能实现吗?或者我必须在应用程序层执行此操作?
表定义
CREATE TABLE `prod`.`parent` (
`SP_ID` varchar(20) NOT NULL COMMENT '',
`SP_NAME` varchar(100) NOT NULL COMMENT '',
`DELETE_FLG` tinyint(1) NOT NULL DEFAULT '0' COMMENT '',
PRIMARY KEY (`SP_ID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT=''
CREATE TABLE `prod`.`child` (
`SP_ID` varchar(20) NOT NULL COMMENT '',
`JOB_KEY` varchar(11) NOT NULL,
PRIMARY KEY (`SP_ID`,`JOB_KEY`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT=''
CREATE TABLE `stg`.`parent` (
`SP_ID` varchar(20) NOT NULL COMMENT '',
`SP_NAME` varchar(100) NOT NULL COMMENT '',
`DELETE_FLG` tinyint(1) NOT NULL DEFAULT '0' COMMENT '',
PRIMARY KEY (`SP_ID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT=''