如何用SQLite递归CTE结合PRAGMA foreign_key_list枚举所有父表？

需求 #

需要枚举指定表（如grandChild）的所有直接父表、间接父表（即上层所有层级的父表）。现有SQLite脚本创建了parent→child→grandChild的层级结构，但递归CTE无法正常运行。

原脚本（问题版本） #

PRAGMA foreign_keys = on; 
 
create table parent( 
id INTEGER PRIMARY KEY 
); 
 
create table child 
( 
id INTEGER PRIMARY KEY, 
parentId REFERENCES parent(id) 
); 
 
create table grandChild 
( 
id INTEGER PRIMARY KEY, 
childId REFERENCES child(id) 
); 
 
.mode column 
.header on 
select 'parent' as childTable, "from" as childField, "table" as parentTable, "to" as parentField 
from pragma_foreign_key_list('parent'); 
 
select  'child' as childTable, "from" as childField, "table" as parentTable, "to" as parentField 
from pragma_foreign_key_list('child'); 
 
select '' as ""; 
 
select  'grandChild' as childTable, "from" as childField, "table" as parentTable, "to" as parentField 
from pragma_foreign_key_list('grandChild'); 
 
select '' as ""; 
 
select distinct "table" as tName from pragma_foreign_key_list('grandChild'); 
 
select '' as ""; 
 
with recursive tabs as ( 
select distinct "table" as tName from pragma_foreign_key_list('grandChild') 
union all 
select distinct "table" from pragma_foreign_key_list(quote(select tName from tabs)) 
) 
select * from tabs; 

问题分析 #

原递归CTE的核心错误：

• 递归步骤中pragma_foreign_key_list(quote(select tName from tabs))语法无效，不能在pragma的参数中嵌套SELECT查询，无法正确引用递归层的表名。
• 未通过关联查询处理每个父表的外键获取逻辑。

正确实现方案 #

方案1：枚举所有层级的父子表关系（含字段映射）

这个查询会返回从目标表到所有上层父表的完整外键关联路径：

PRAGMA foreign_keys = on; 
 
-- 保留原表结构创建语句
create table parent( 
id INTEGER PRIMARY KEY 
); 
 
create table child 
( 
id INTEGER PRIMARY KEY, 
parentId REFERENCES parent(id) 
); 
 
create table grandChild 
( 
id INTEGER PRIMARY KEY, 
childId REFERENCES child(id) 
); 
 
.mode column 
.header on

-- 递归CTE获取所有层级的父表关系
WITH RECURSIVE parent_hierarchy AS (
  -- 初始步骤：获取目标表（grandChild）的直接父表
  SELECT 
    'grandChild' AS child_table,
    "from" AS child_field,
    "table" AS parent_table,
    "to" AS parent_field
  FROM pragma_foreign_key_list('grandChild')
  UNION ALL
  -- 递归步骤：遍历当前父表的上层父表
  SELECT
    ph.parent_table AS child_table,
    fk."from" AS child_field,
    fk."table" AS parent_table,
    fk."to" AS parent_field
  FROM parent_hierarchy ph
  -- 对递归层的每个父表查询其外键关联
  JOIN pragma_foreign_key_list(ph.parent_table) fk
)
-- 输出所有层级的关联关系
SELECT * FROM parent_hierarchy;

方案2：仅枚举所有父表名称（去重）

如果只需要获取所有上层父表的名称，不需要字段映射，可以用更简洁的写法：

WITH RECURSIVE parent_tables AS (
  -- 初始：获取目标表的直接父表
  SELECT DISTINCT "table" AS table_name
  FROM pragma_foreign_key_list('grandChild')
  UNION ALL
  -- 递归：获取当前父表的父表
  SELECT DISTINCT fk."table" AS table_name
  FROM parent_tables pt
  JOIN pragma_foreign_key_list(pt.table_name) fk
)
SELECT * FROM parent_tables;

说明 #

• 递归CTE的核心是通过JOIN pragma_foreign_key_list(ph.parent_table)来动态查询每个父表的外键关联，避免了原脚本的语法错误。
• 使用DISTINCT可以避免因多外键关联导致的重复结果（如果表有多个外键指向同一父表）。

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