时间戳

首页 > 解决方案 > 如何在 SQLite 中具有匹配列的 2 个现有表之间创建外键？

sql - 如何在 SQLite 中具有匹配列的 2 个现有表之间创建外键？

问题描述

我正在处理一些我需要找到它们之间的“差异”的excel文件。我首先以编程方式使用它们创建 2 个 SQLite 表。有两个文件具有相同的列，并且具有匹配的行。问题是这些行不一定是按顺序排列的，并且没有一个键列可以匹配这些行。但是，我可以同时加入四列。

我目前查找两个表之间差异的解决方案是这个查询：

SELECT `field1`, `field2`, etc FROM table1
WHERE NOT EXISTS (SELECT 1 FROM table2
    WHERE table2.`SID` = table1.`SID`
    AND table2.`BID` = table1.`BID`
    AND table2.`OID` = table1.`OID`
    AND table2.`LID` = table1.`LID`);

我希望结果是 table1 中的字段和 table2 中每条记录的字段，所以我最终得到了两倍的列。

字段1 字段2 字段3
美国广播公司 定义 吉
jkl mno pqr
字段1 字段2 字段3
美国广播公司 定义 AAA
jkl mno pqr

到一个所有列仅显示不同行的表：

t1_field1 t1_field2 t1_field3 t2_field1 t2_field2 t2_field3
美国广播公司 定义 吉 美国广播公司 定义 AAA

我认为比一些复杂的 sql 更好的方法是为两个表追溯创建一个外键，这样进行这样的查询会更简单。

请记住，我创建这些表并以编程方式插入数据，因此如果解决方案位于创建过程中，我仍然可以实施您的建议。

编辑： 正如@marcos 指出的那样，由于不能保证匹配行存在，外键是不可能的。复合连接听起来像是一条好路。

我的目的是找出从 table1 到 table2 的任何差异，包括删除一行。我不需要从 2 到 1 的差异。

@marcos 推荐的这个查询（减去 t2 - t1 联合）几乎可以完美运行：

SELECT t1.field1, t1.field2, ..., t2.field1, t2.field2
FROM table1 t1
  LEFT JOIN table2 t2
    ON  t1.SID = t2.SID
    AND t1.BID = t2.BID
    AND t1.OID = t2.OID
    AND t1.LID = t2.LID
WHERE t2.SID IS NULL

返回类似：

t1_field1 t1_field2 t1_field3 t2_field1 t2_field2 t2_field3
美国广播公司 定义 吉 --- --- ---

但不幸的是，table2 中的列没有填充。我也不确定WHERE t2.SID IS NULL这里的工作原理，因为 SID 不是唯一键。

提前致谢！:)

标签： sqlsqlitejoinforeign-keys

解决方案

如果我理解正确，您需要 table1 中与 table2 不匹配的所有行加上 table2 中与 t1 不匹配的所有行。

SELECT t1.field1, t1.field2, ..., t2.field1, t2.field2
FROM table1 t1
  LEFT JOIN table2 t2
    ON  t1.SID = t2.SID
    AND t1.BID = t2.BID
    AND t1.OID = t2.OID
    AND t1.LID = t2.LID
WHERE t2.SID IS NULL

UNION ALL

SELECT t1.field1, t1.field2, ..., t2.field1, t2.field2
FROM table2 t2
  LEFT JOIN table1 t1
    ON  t1.SID = t2.SID
    AND t1.BID = t2.BID
    AND t1.OID = t2.OID
    AND t1.LID = t2.LID
WHERE t1.SID IS NULL

在这四列（SID、BID、OID 和 LID）上创建索引以提高性能。

外键是不可能的，因为它们强制一个表上的数据存在于另一个表中。如果您尝试创建一个 fk 约束错误，您将收到一个错误。

推荐阅读