SQLite列出数据库中所有外键

Question

有没有一种方法可以列出SQLite数据库中所有外键？它们似乎没有存储在sqlite_master中，而 PRAGMA foreign_key_list('table') 一次只能列出一个外键。或者，有没有一种方法可以列出参考某个表的所有外键？

- Fergal Moran

---

虽然不是一个完美的解决方案，但仍然是一个解决方案，用于“是否有一种方法列出引用某个表的外键？”-> sqlite3 test.sqlite .dump | less -j.99 然后搜索“REFERENCES destTable”。 - undefined

Answer 1

似乎所有（或许多）的PRAGMA命令都可以通过一点小技巧进行程序化选择；

通常它们被称为：

PRAGMA table_info('my_table');
PRAGMA foreign_key_list('my_table');

但这也可以做到：

SELECT * FROM pragma_table_info('my_table');
SELECT * FROM pragma_foreign_key_list('my_table');

而且可以（或多或少地）获取到模式：

.schema pragma_table_info
/* pragma_table_info(cid,name,type,"notnull",dflt_value,pk) */;

.schema pragma_foreign_key_list
/* pragma_foreign_key_list(id,seq,"table","from","to",on_update,on_delete,"match") */

所以，要获取所有的外键，可以通过sqlite_master和pragma_foreign_key_list之间的JOIN来实现。

SELECT 
    m.name
    , p.*
FROM
    sqlite_master m
    JOIN pragma_foreign_key_list(m.name) p ON m.name != p."table"
WHERE m.type = 'table'
ORDER BY m.name
;

只要注意，`pragma_foreign_key_list` 的一些字段，如 `table`、`from` 等，**必须**加引号；

第三方编辑：

对于来自 dbeaver 的 SQLite 示例数据库的上述查询结果。

看起来就是这样

- Francisco Puga

---

你能不能省略 ON m.name != p."table"，因为SQLite允许（INNER）JOIN不带ON子句？（将其视为ON 1=1，就像CROSS JOIN一样。） - urbanSoft

据我所知，这被称为自然连接(Natural Join)，在SQLite中自动测试两个表中每个列的值是否相等。因此，在这里我认为 ON 不能省略。而且作为个人偏好，我更喜欢打更多的字符并明确发生了什么。 - Francisco Puga

明确性在很多方面都是金子般珍贵的，但是此 ON 指令使得请求有关操作的部分错误，因为它排除了所有指向自身表格的外键。您可能想要将其移除。 - Tom Ah

Answer 2

使用SQLite shell，使用.schema指令，并使用GREP过滤包含REFERENCES的行。

从SQLite存储库中的shell.c文件，今天在主干版本中有两个查询：

SELECT sql
  FROM (
        SELECT sql sql, type type, tbl_name tbl_name, name name
          FROM sqlite_master
         UNION ALL
        SELECT sql, type, tbl_name, name
          FROM sqlite_temp_master
       )
 WHERE tbl_name LIKE shellstatic()
   AND type != 'meta'
   AND sql NOTNULL
 ORDER BY substr(type, 2, 1), name

而且

SELECT sql
  FROM (
        SELECT sql sql, type type, tbl_name tbl_name, name name
          FROM sqlite_master
         UNION ALL
        SELECT sql, type, tbl_name, name
          FROM sqlite_temp_master
       )
 WHERE type != 'meta'
   AND sql NOTNULL
   AND name NOT LIKE 'sqlite_%'
 ORDER BY substr(type, 2, 1), name

第二个可能是你正在寻找的东西。

- Benoit

---

嗨，谢谢Benoit。我应该提到我将在Android客户端上执行此操作，因此无法访问SQLite shell。 :( - Fergal Moran

嗨Benoit - 这给了我所有表的完整ddl，这肯定很有用，但仍然让我面临解析外键特定内容的问题。在“SELECT tbl_name FROM sqlite_master WHERE type ='table'”的结果上调用PRAGMA foreign_keys可能更容易，但我正在尝试找到更简单的方法来做到这一点？ - Fergal Moran

@Fergal Moran：然而这仍然是我能想到的最好的方法。 - Benoit

@FergalMoran：你描述的方法（从sqlite_master获取表名，然后为每个表调用PRAGMA foreign_keys）是最简单的方法。不幸的是，PRAGMA结果无法与SELECT语句合并... - Martijn

@Benoit 谢谢你的帮助，我现在会坚持使用 PRAGMA 方法，因为我认为这是最简单的方法。 - Fergal Moran

列出一张表的外键正确的命令是 PRAGMA foreign_key_list，而不是 PRAGMA foreign_keys。 - MPelletier