含复合主键的MySQL AFTER UPDATE触发器执行失败问题排查

需求 #

需要将lh_psalm_join表的变更保存到sync_lh_psalm_join表，实现与Android应用的数据同步。

表结构 #

主表 lh_psalm_join #

CREATE TABLE `lh_psalm_join` (
  `groupFK` int(11) NOT NULL,
  `readingFK` int(11) NOT NULL,
  `theOrder` int(11) NOT NULL,
  `themeFK` int(11) DEFAULT NULL,
  `epigraphFK` int(11) DEFAULT NULL,
  `thePart` int(11) DEFAULT NULL,
  PRIMARY KEY (`groupFK`,`readingFK`),
  KEY `readingFK` (`readingFK`),
  KEY `epigraphFK` (`epigraphFK`),
  KEY `themeFK` (`themeFK`),
  CONSTRAINT `lh_psalm_join_ibfk_1` FOREIGN KEY (`groupFK`) REFERENCES `lh_psalmody_join` (`groupID`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `lh_psalm_join_ibfk_2` FOREIGN KEY (`readingFK`) REFERENCES `lh_psalm` (`psalmID`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `lh_psalm_join_ibfk_3` FOREIGN KEY (`epigraphFK`) REFERENCES `lh_epigraph` (`epigraphID`) ON DELETE SET NULL ON UPDATE CASCADE,
  CONSTRAINT `lh_psalm_join_ibfk_4` FOREIGN KEY (`themeFK`) REFERENCES `lh_theme` (`themeID`) ON DELETE SET NULL ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_spanish_ci

同步表 sync_lh_psalm_join #

CREATE TABLE `sync_lh_psalm_join` (
  `groupFK` int(11) NOT NULL,
  `readingFK` int(11) NOT NULL,
  `crud` char(1) DEFAULT NULL,
  `lastUpdate` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
  PRIMARY KEY (`groupFK`,`readingFK`),
  CONSTRAINT `sync_lh_psalm_join_ibfk_1` FOREIGN KEY (`groupFK`, `readingFK`) REFERENCES `lh_psalm_join` (`groupFK`, `readingFK`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_spanish_ci

触发器定义 #

为实现同步，创建了以下AFTER UPDATE触发器：

DROP TRIGGER IF EXISTS `lh_psalm_join_after_update`;
DELIMITER $$
CREATE TRIGGER `lh_psalm_join_after_update` AFTER UPDATE ON `lh_psalm_join` FOR EACH ROW
BEGIN
  DECLARE countRows INT(11) DEFAULT 0;
  SELECT 
    COUNT(*) 
  FROM `sync_lh_psalm_join` 
  WHERE 
    `groupFK`=NEW.`groupFK` AND 
    `readingFK`=NEW.`readingFK` 
  INTO countRows;
  IF countRows>0 THEN
    UPDATE `sync_lh_psalm_join` SET 
      `groupFK`=NEW.`groupFK`,
      `readingFK`=NEW.`readingFK`,
      `crud`='u', 
      `lastUpdate`=CURRENT_TIMESTAMP 
    WHERE 
      `groupFK`=OLD.`groupFK` AND 
      `readingFK`=OLD.`readingFK`;
  ELSE
    INSERT INTO `sync_lh_psalm_join` 
      (`groupFK`,`readingFK`,`crud`) 
    VALUES
      (NEW.`groupFK`,NEW.`readingFK`,'u');
  END IF;
  CALL spUpdateSyncStatus('lh_psalm_join');
END$$
DELIMITER ;

错误信息 #

执行lh_psalm_join表的更新操作时，若同步表中已有对应主键记录，触发以下错误：

Cannot delete or update a parent row: a foreign key constraint fails
(c39b075_deiverbu.sync_lh_psalm_join, CONSTRAINT
sync_lh_psalm_join_ibfk_1 FOREIGN KEY (groupFK, readingFK)
REFERENCES lh_psalm_join (groupFK, readingFK))

即使将UPDATE语句的WHERE条件改为匹配NEW主键值，错误仍然存在。

核心问题 #

1. 主键更新的逻辑矛盾：试图在sync_lh_psalm_join中更新主键字段（groupFK和readingFK），但这两个字段同时是外键，关联到lh_psalm_join的主键。当执行UPDATE sync_lh_psalm_join SET groupFK=NEW.groupFK, readingFK=NEW.readingFK WHERE ...时，MySQL会先检查新主键值是否存在于主表——但此时主表的UPDATE操作已完成，旧主键值的行已被移除，用旧主键匹配的同步表记录在更新主键时，会临时导致外键指向的主表记录不存在，从而触发约束错误。

2. 业务逻辑误区：主表lh_psalm_join的复合主键(groupFK, readingFK)作为行的唯一标识，正常业务中不应被修改。修改主键本质上等同于删除旧行、插入新行，对应的同步逻辑也应匹配这个操作。

修正方案 #

方案1：禁止修改主表主键（推荐） #

主键的作用是唯一标识数据行，不应该被修改。如果业务上需要调整关联关系，应删除旧的lh_psalm_join行，再插入新行。此时只需通过AFTER INSERT和AFTER DELETE触发器维护同步表即可，无需处理主键更新的场景。

方案2：适配主键修改的同步逻辑 #

如果确实需要支持主键更新，修改触发器逻辑，将“更新同步表主键”改为“删除旧主键记录+插入新主键记录”，用REPLACE INTO简化操作：

DROP TRIGGER IF EXISTS `lh_psalm_join_after_update`;
DELIMITER $$
CREATE TRIGGER `lh_psalm_join_after_update` AFTER UPDATE ON `lh_psalm_join` FOR EACH ROW
BEGIN
  -- 主键发生变化时，先删除旧主键的同步记录
  IF NOT (OLD.groupFK = NEW.groupFK AND OLD.readingFK = NEW.readingFK) THEN
    DELETE FROM `sync_lh_psalm_join` 
    WHERE `groupFK`=OLD.`groupFK` AND `readingFK`=OLD.`readingFK`;
  END IF;

  -- 插入或更新同步记录：存在则替换，不存在则插入
  REPLACE INTO `sync_lh_psalm_join` 
    (`groupFK`, `readingFK`, `crud`) 
  VALUES
    (NEW.`groupFK`, NEW.`readingFK`, 'u');

  CALL spUpdateSyncStatus('lh_psalm_join');
END$$
DELIMITER ;

REPLACE INTO会自动检查主键是否存在：存在则删除旧记录再插入新记录，不存在则直接插入，完美适配主键变化的场景。提前判断主键是否变化可避免不必要的删除操作，提升效率。

方案3：临时关闭外键检查（不推荐） #

若不想修改触发器逻辑，可临时关闭外键检查，但这会破坏数据一致性，仅适合测试场景，生产环境禁用：

SET FOREIGN_KEY_CHECKS=0;
-- 执行更新操作
SET FOREIGN_KEY_CHECKS=1;

内容的提问来源于stack exchange，提问作者A. Cedano