SQL外键匹配错误求助：contracts_tb关联多表问题排查

嘿，作为SQL新手遇到外键问题太正常了，我来帮你拆解清楚问题根源，以及怎么调整才对：

为什么会出现「foreign key mismatch」错误？ #

SQLite对外键的引用列有硬性要求：被外键关联的列必须是对应表的主键，或者被标记为UNIQUE（唯一约束）。你现在的contracts_tb里，尝试把media_typeref关联到mediaadv_tb的mtype、media_titleref关联到title，但这两个字段既不是主键，也没有加唯一约束——SQLite没法确定你要引用的是哪一条数据，自然就抛出错误了。

你根本不需要把这些字段都存为外键！ #

你的需求是「关联显示父表的内容」，但完全没必要把mediatitle、mediatype、cus_company这些字段冗余存到contracts_tb里。正确的思路是：只在contracts_tb里存父表主键的外键引用，然后通过JOIN查询来关联获取所有需要的字段，这样既避免数据冗余，也不会出现外键错误。

调整后的正确表结构 #

我帮你修正了contracts_tb的结构，只保留必要的外键关联：

-- 先删除旧表（如果存在）
DROP TABLE IF EXISTS customer_tb;
CREATE TABLE IF NOT EXISTS customer_tb (
 cus_id INTEGER PRIMARY KEY,
 company TEXT,
 firstname TEXT,
 middlename TEXT,
 lastname TEXT,
 gender TEXT,
 dob TEXT,
 dateregistered TEXT,
 contactno TEXT,
 emailaddress TEXT,
 description TEXT,
 refpic INTEGER,
 cuspic BLOB
);

DROP TABLE IF EXISTS mediaadv_tb;
CREATE TABLE IF NOT EXISTS mediaadv_tb (
 med_id INTEGER PRIMARY KEY,
 mtype TEXT,
 duration TEXT,
 title TEXT,
 dateadded TEXT,
 desription TEXT,
 previewimg BLOB,
 filepath TEXT
);

DROP TABLE IF EXISTS contracts_tb;
CREATE TABLE IF NOT EXISTS contracts_tb (
 contract_id INTEGER PRIMARY KEY,
 -- 只保留父表主键的外键引用
 customer_idref INTEGER REFERENCES customer_tb (cus_id),
 media_idref INTEGER REFERENCES mediaadv_tb (med_id),
 status TEXT,
 priority TEXT,
 dateadded TEXT,
 dateexpiration TEXT
 -- 其他你需要的业务字段可以继续在这里添加
);

如何关联查询获取所有需要的内容？ #

现在你可以用JOIN语句，一次性查询出合同关联的客户和媒体广告的所有信息：

SELECT
  c.contract_id,
  c.status,
  c.priority,
  c.dateadded,
  c.dateexpiration,
  -- 获取客户表的关联信息
  cus.cus_id,
  cus.company AS cus_company,
  cus.firstname,
  cus.lastname,
  -- 获取媒体广告表的关联信息
  med.med_id,
  med.mtype AS media_type,
  med.title AS media_title,
  med.duration
FROM contracts_tb c
LEFT JOIN customer_tb cus ON c.customer_idref = cus.cus_id
LEFT JOIN mediaadv_tb med ON c.media_idref = med.med_id;

这样就能得到你想要的所有关联内容，而且数据是实时同步的——如果父表（比如customer_tb的company）更新了，查询结果也会自动更新，不会出现数据不一致的问题。

关于「父表」的疑问 #

你其实已经创建了正确的父表（customer_tb和mediaadv_tb），它们就是contracts_tb的关联父表。之前的问题不是缺少父表，而是外键引用的列不符合SQLite的要求，以及设计思路上的冗余存储问题。

（不推荐）如果非要冗余存储这些字段怎么办？ #

如果你因为某些特殊原因，一定要把media_type、media_title这些字段存在contracts_tb里，那必须给mediaadv_tb的对应字段加上UNIQUE约束，但我强烈不建议这么做——因为如果父表的title或mtype修改了，你还要手动更新contracts_tb里的冗余数据，很容易出错：

-- 修改mediaadv_tb，给mtype和title加唯一约束（不推荐）
CREATE TABLE IF NOT EXISTS mediaadv_tb (
 med_id INTEGER PRIMARY KEY,
 mtype TEXT UNIQUE,
 duration TEXT,
 title TEXT UNIQUE,
 dateadded TEXT,
 desription TEXT,
 previewimg BLOB,
 filepath TEXT
);

内容的提问来源于stack exchange，提问作者Courièr Rob