SQLite主表更新secondaryId后关联表未同步的问题

问题原因 #

你遇到的问题核心在于两点：

1. INSERT OR REPLACE并非UPDATE操作：该语句会先删除表a中的目标行，再插入新行，而非直接修改原有行。外键的ON UPDATE CASCADE仅在执行UPDATE语句时触发，因此无法同步更新表b的字段。
2. NULL值的外键匹配规则：SQLite中NULL不等于任何值（包括另一个NULL）。即使改用UPDATE语句修改表a的secondaryId，由于表b中parentSecondaryId初始为NULL，无法与表a原有的NULL值匹配，单字段外键或联合外键的ON UPDATE CASCADE都无法触发同步。

解决方案：使用触发器实现同步更新 #

最可靠的方式是创建AFTER UPDATE触发器，当表a的secondaryId被更新时，自动同步更新表b中对应行的parentSecondaryId。

具体操作步骤 #

1. 开启外键约束（已在你的示例中执行）：

pragma foreign_keys = on;

2. 创建原有表结构：

create table a (
primaryId TEXT PRIMARY KEY NOT NULL UNIQUE,
secondaryId TEXT DEFAULT NULL UNIQUE,
UNIQUE(primaryId, secondaryId));

create table b (
id TEXT PRIMARY KEY NOT NULL UNIQUE,
parentPrimaryId REFERENCES a(primaryId) ON UPDATE CASCADE,
parentSecondaryId REFERENCES a(secondaryId));

3. 创建同步触发器：

CREATE TRIGGER sync_a_secondary_to_b
AFTER UPDATE OF secondaryId ON a
FOR EACH ROW
BEGIN
  UPDATE b
  SET parentSecondaryId = NEW.secondaryId
  WHERE b.parentPrimaryId = NEW.primaryId;
END;

4. 使用UPDATE语句修改表a的secondaryId（替代INSERT OR REPLACE）：

UPDATE a SET secondaryId = "a_secondary_id" WHERE primaryId = "a_primary_id";

5. 查询表b，即可看到parentSecondaryId已同步更新：

SELECT * FROM b;

执行结果：

b_id|a_primary_id|a_secondary_id

补充说明 #

• 若必须使用INSERT OR REPLACE，可额外创建AFTER DELETE触发器，在表a原行被删除时同步更新表b字段，但这种方式不如UPDATE+触发器简洁可靠。
• 联合外键无法解决问题的原因：当表a原行被INSERT OR REPLACE删除时，联合外键的ON DELETE规则会触发删除表b的行（而非更新）；若使用UPDATE，由于NULL值不匹配，联合外键的ON UPDATE CASCADE也无法触发同步。

内容的提问来源于stack exchange，提问作者Dmitry