SQLite插入数据时Foreign Key Mismatch问题求助

你的问题根源在于Videos表的id列没有被定义为主键或唯一约束。SQLite要求外键引用的列必须是被引用表的主键，或者拥有唯一索引，否则会触发foreign key mismatch错误。

问题分析 #

看你提供的表结构：

• Videos表的id列仅声明为INTEGER，没有PRIMARY KEY或UNIQUE约束，不符合外键引用的规则。
• 你提到Roles表能正常引用Videos的id，大概率是以下两种情况之一：
  • 创建Roles表时，SQLite的外键约束未开启（SQLite默认禁用外键，需通过PRAGMA foreign_keys = ON;手动开启），因此外键检查未生效；
  • 后续给Videos表的id列添加了主键/唯一约束，但Chapters表的外键约束检查时，未同步识别到该变更。

修复方案 #

方法1：将Videos表的id设为主键

如果Videos表无数据或可重建，直接修改创建语句：

CREATE TABLE "Videos" (
    [id] INTEGER PRIMARY KEY, 
    [title] text NOT NULL, 
    [yr] INT NOT NULL, 
    [runtime] INT NOT NULL, 
    [descp] text NULL, 
    [cats] text NOT NULL, 
    [creators] text NOT NULL, 
    [creator_roles] text NULL, 
    [thumbnail] BLOB NOT NULL DEFAULT (x''), 
    [file_location] text NOT NULL
);

如果表已有数据，SQLite不支持直接修改主键，需通过重建表迁移数据：

-- 创建临时表备份数据
CREATE TABLE Videos_temp AS SELECT * FROM Videos;
-- 删除原表
DROP TABLE Videos;
-- 重新创建带主键的表
CREATE TABLE "Videos" (
    [id] INTEGER PRIMARY KEY, 
    [title] text NOT NULL, 
    [yr] INT NOT NULL, 
    [runtime] INT NOT NULL, 
    [descp] text NULL, 
    [cats] text NOT NULL, 
    [creators] text NOT NULL, 
    [creator_roles] text NULL, 
    [thumbnail] BLOB NOT NULL DEFAULT (x''), 
    [file_location] text NOT NULL
);
-- 恢复数据
INSERT INTO Videos SELECT * FROM Videos_temp;
-- 删除临时表
DROP TABLE Videos_temp;

方法2：给Videos的id列添加唯一索引

如果不想修改表结构设为主键，可给id列添加唯一索引，同样满足外键引用要求：

CREATE UNIQUE INDEX idx_videos_id ON Videos(id);

完成上述操作后，重新执行Insert into Chapters(video_id) VALUES (470114);即可正常工作。

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