当前位置：文江博客话题详情

SQLite 列出数据库中的所有外键

发布于 2024-10-29 18:37:22 字数 151 浏览 0 评论 0原文

有没有办法列出 SQLite 数据库中的所有外键？

它们似乎没有存储在 sqlite_master 中，并且 PRAGMAforeign_key_list('table') 一次只列出一个。

或者，有没有办法列出哪些外键引用了表？

原文

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

や莫失莫忘 2024-11-05 18:37:22

似乎所有（或许多）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 可以解决这个问题：

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、 ...必须被引用；

3rd party edit

上面对 sqlite 示例数据库的查询结果来自 dbeaver

看起来像这样

表及其关系的查询结果 < /a>

It seems that all (or many) of the PRAGMA commands can be programatically selected with a little trick;

Usually the are called like:

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

But this can also be done:

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

And the schema can also be (more or less) obtained:

.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") */

So, to get all the fks a JOIN between sqlite_master and pragma_foreign_key_list can do the trick:

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
;

Just take care, that some fields of pragma_foreign_key_list like table, from, ... must be quoted;

3rd party edit

The result of the query above for the sqlite sample database from dbeaver

looks like this

回复收藏 0 原文

别低头，皇冠会掉 2024-11-05 18:37:22

在 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

第二个可能是您正在寻找的内容。

With the SQLite shell, use the .schema instruction, and use GREP to filter lines containing REFERENCES.

From shell.c in the SQLite repository, today's version in the trunk, two queries:

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

and

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

The second one is probably what you are looking for.

回复收藏 0 原文

~没有更多了~