SQLite使用rowid作为外键时触发外键不匹配错误的原因咨询

你遇到的外键不匹配报错，和查询到的member表rowid真实存在没有关联，本质是SQLite对外键引用目标列的规则限制。你之前了解的“外键必须引用完整的主键或唯一键/索引”的要求并没有错，只是对rowid的可引用条件存在认知偏差：

• SQLite的rowid虽然在普通表中默认是唯一、非空的行标识，但它只有在没有自定义主键的情况下，才会自动成为表的隐式主键，具备被外键引用的资格。
• 一旦你给表显式定义了由普通业务列组成的自定义主键（不管是单列还是复合主键），rowid就会从隐式主键降级为普通隐藏列，不会自动获得“可作为外键引用目标”的身份——哪怕它的值依然是全局唯一、非空的，也不满足SQLite的外键校验规则。
• 你当前的member表用(id, platform_id)这个业务字段组合做了复合主键，rowid本身没有被声明为主键，也没有被加唯一约束/唯一索引，自然不能作为外键的合法引用目标，所以哪怕你存的1是真实存在的rowid值，也会触发外键校验失败。

你的member表建表语句由业务字段组成了复合主键：

CREATE TABLE IF NOT EXISTS 'main'.'member' (
    'id'    TEXT NOT NULL,
    'platform_id'   INTEGER NOT NULL,
    PRIMARY KEY('id', 'platform_id')
);

这种结构下，虽然表没有加WITHOUT ROWID选项，插入记录后也会正常生成自增rowid（你查询到值为1的记录就是这个机制的结果），但SQLite不会把这个隐藏的rowid列识别为合法的外键引用目标，这就是触发foreign key mismatch报错的根本来源。

• 方式1：在不改动现有表结构的前提下，给rowid补一个显式唯一索引，让它满足外键引用的目标要求。执行一次建索引的语句即可：

  CREATE UNIQUE INDEX idx_member_rowid ON member(rowid);
  

  索引创建完成后，之前的插入语句就可以正常执行了。注意rowid是SQLite的内置隐藏列，建索引时不需要在表结构中单独声明这个字段。
• 方式2：调整表结构，不要隐式依赖隐藏的rowid，直接显式声明自增主键列。这种方式的语义更清晰，也更符合通用数据库的设计习惯：

  -- 调整后的member表结构，显式声明自增主键
  CREATE TABLE IF NOT EXISTS 'main'.'member' (
      'member_id' INTEGER PRIMARY KEY AUTOINCREMENT,
      'id'    TEXT NOT NULL,
      'platform_id'   INTEGER NOT NULL,
      -- 原来的复合业务主键改成唯一约束，保留原有的业务唯一性校验
      UNIQUE('id', 'platform_id')
  );
  

  调整后，character表的外键直接引用member.member_id即可，不需要依赖SQLite的隐藏列特性。
• 方式3：如果不想加额外索引、也不想改表结构，就直接把外键的引用目标换成member表实际定义的复合主键。这种场景下character表需要同步存储member的id和platform_id两个字段，用复合关联条件匹配，不再单独存储rowid。

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