是否有一种方法使用SQL列出给定表的所有外键?我知道表名/模式,我可以把它插入。
当前回答
您可以通过information_schema表来实现这一点。例如:
SELECT
tc.table_schema,
tc.constraint_name,
tc.table_name,
kcu.column_name,
ccu.table_schema AS foreign_table_schema,
ccu.table_name AS foreign_table_name,
ccu.column_name AS foreign_column_name
FROM
information_schema.table_constraints AS tc
JOIN information_schema.key_column_usage AS kcu
ON tc.constraint_name = kcu.constraint_name
AND tc.table_schema = kcu.table_schema
JOIN information_schema.constraint_column_usage AS ccu
ON ccu.constraint_name = tc.constraint_name
AND ccu.table_schema = tc.table_schema
WHERE tc.constraint_type = 'FOREIGN KEY' AND tc.table_name='mytable';
其他回答
我创建了一个小工具来查询和比较数据库模式: Dump PostgreSQL数据库模式到文本
有关于FK的信息,但ollyc的回复提供了更多的细节。
现有的答案都没有给出我想要的结果。这是我的(庞大的)查询,用于查找有关外键的信息。
注意事项:
The expressions used to generate from_cols and to_cols could be vastly simplified on Postgres 9.4 and later using WITH ORDINALITY rather than the window-function-using hackery I'm using. Those same expressions are relying on the query planner not altering the returned order of results from UNNEST. I don't think it will, but I don't have any multiple-column foreign keys in my dataset to test with. Adding the 9.4 niceties eliminates this possibility altogether. The query itself requires Postgres 9.0 or later (8.x didn't allow ORDER BY in aggregate functions) Replace STRING_AGG with ARRAY_AGG if you want an array of columns rather than a comma-separated string.
-
SELECT
c.conname AS constraint_name,
(SELECT n.nspname FROM pg_namespace AS n WHERE n.oid=c.connamespace) AS constraint_schema,
tf.name AS from_table,
(
SELECT STRING_AGG(QUOTE_IDENT(a.attname), ', ' ORDER BY t.seq)
FROM
(
SELECT
ROW_NUMBER() OVER (ROWS UNBOUNDED PRECEDING) AS seq,
attnum
FROM
UNNEST(c.conkey) AS t(attnum)
) AS t
INNER JOIN pg_attribute AS a ON a.attrelid=c.conrelid AND a.attnum=t.attnum
) AS from_cols,
tt.name AS to_table,
(
SELECT STRING_AGG(QUOTE_IDENT(a.attname), ', ' ORDER BY t.seq)
FROM
(
SELECT
ROW_NUMBER() OVER (ROWS UNBOUNDED PRECEDING) AS seq,
attnum
FROM
UNNEST(c.confkey) AS t(attnum)
) AS t
INNER JOIN pg_attribute AS a ON a.attrelid=c.confrelid AND a.attnum=t.attnum
) AS to_cols,
CASE confupdtype WHEN 'r' THEN 'restrict' WHEN 'c' THEN 'cascade' WHEN 'n' THEN 'set null' WHEN 'd' THEN 'set default' WHEN 'a' THEN 'no action' ELSE NULL END AS on_update,
CASE confdeltype WHEN 'r' THEN 'restrict' WHEN 'c' THEN 'cascade' WHEN 'n' THEN 'set null' WHEN 'd' THEN 'set default' WHEN 'a' THEN 'no action' ELSE NULL END AS on_delete,
CASE confmatchtype::text WHEN 'f' THEN 'full' WHEN 'p' THEN 'partial' WHEN 'u' THEN 'simple' WHEN 's' THEN 'simple' ELSE NULL END AS match_type, -- In earlier postgres docs, simple was 'u'nspecified, but current versions use 's'imple. text cast is required.
pg_catalog.pg_get_constraintdef(c.oid, true) as condef
FROM
pg_catalog.pg_constraint AS c
INNER JOIN (
SELECT pg_class.oid, QUOTE_IDENT(pg_namespace.nspname) || '.' || QUOTE_IDENT(pg_class.relname) AS name
FROM pg_class INNER JOIN pg_namespace ON pg_class.relnamespace=pg_namespace.oid
) AS tf ON tf.oid=c.conrelid
INNER JOIN (
SELECT pg_class.oid, QUOTE_IDENT(pg_namespace.nspname) || '.' || QUOTE_IDENT(pg_class.relname) AS name
FROM pg_class INNER JOIN pg_namespace ON pg_class.relnamespace=pg_namespace.oid
) AS tt ON tt.oid=c.confrelid
WHERE c.contype = 'f' ORDER BY 1;
以下是Andreas Joseph Krogh从PostgreSQL邮件列表提供的解决方案:http://www.postgresql.org/message-id/200811072134.44750.andreak@officenet.no
SELECT source_table::regclass, source_attr.attname AS source_column,
target_table::regclass, target_attr.attname AS target_column
FROM pg_attribute target_attr, pg_attribute source_attr,
(SELECT source_table, target_table, source_constraints[i] source_constraints, target_constraints[i] AS target_constraints
FROM
(SELECT conrelid as source_table, confrelid AS target_table, conkey AS source_constraints, confkey AS target_constraints,
generate_series(1, array_upper(conkey, 1)) AS i
FROM pg_constraint
WHERE contype = 'f'
) query1
) query2
WHERE target_attr.attnum = target_constraints AND target_attr.attrelid = target_table AND
source_attr.attnum = source_constraints AND source_attr.attrelid = source_table;
这个解决方案处理引用多个列的外键,并避免重复(其他一些答案无法做到这一点)。我唯一改变的是变量名。
下面是一个示例,返回所有引用权限表的雇员列:
SELECT source_column
FROM foreign_keys
WHERE source_table = 'employee'::regclass AND target_table = 'permission'::regclass;
PSQL就是这样做的,如果你用:
psql -E
它将准确地显示执行了哪些查询。在查找外键的情况下,它是:
SELECT conname,
pg_catalog.pg_get_constraintdef(r.oid, true) as condef
FROM pg_catalog.pg_constraint r
WHERE r.conrelid = '16485' AND r.contype = 'f' ORDER BY 1
在这种情况下,16485是我正在寻找的表的oid -你可以通过将你的表名转换为regclass来获得它:
WHERE r.conrelid = 'mytable'::regclass
如果表名不是唯一的(或者是搜索路径中的第一个),则对表名进行模式限定:
WHERE r.conrelid = 'myschema.mytable'::regclass
从最流行的答案改进查询
因为对于postgresql 12+ information_schema是非常慢的
它帮助了我:
SELECT sh.nspname AS table_schema,
tbl.relname AS table_name,
col.attname AS column_name,
referenced_sh.nspname AS foreign_table_schema,
referenced_tbl.relname AS foreign_table_name,
referenced_field.attname AS foreign_column_name
FROM pg_constraint c
INNER JOIN pg_namespace AS sh ON sh.oid = c.connamespace
INNER JOIN (SELECT oid, unnest(conkey) as conkey FROM pg_constraint) con ON c.oid = con.oid
INNER JOIN pg_class tbl ON tbl.oid = c.conrelid
INNER JOIN pg_attribute col ON (col.attrelid = tbl.oid AND col.attnum = con.conkey)
INNER JOIN pg_class referenced_tbl ON c.confrelid = referenced_tbl.oid
INNER JOIN pg_namespace AS referenced_sh ON referenced_sh.oid = referenced_tbl.relnamespace
INNER JOIN (SELECT oid, unnest(confkey) as confkey FROM pg_constraint) conf ON c.oid = conf.oid
INNER JOIN pg_attribute referenced_field ON (referenced_field.attrelid = c.confrelid AND referenced_field.attnum = conf.confkey)
WHERE c.contype = 'f'
推荐文章
- 将一列的多个结果行连接为一列,按另一列分组
- 检查MySQL表是否存在而不使用“select from”语法?
- 使用pgadmin连接到heroku数据库
- 在PostgreSQL中快速发现表的行数
- 更改varchar列的大小为较低的长度
- 从表中选择1是什么意思?
- 如何首次配置postgresql ?
- SQL Server中User和Login的区别
- 数据库性能调优有哪些资源?
- 如何更改表的默认排序规则?
- 为两列的组合添加唯一的约束
- 如何在PostgreSQL中自动更新时间戳
- 设置NOW()为datetime数据类型的默认值?
- 在MySQL中Datetime等于或大于今天
- 当使用JDBC连接到postgres时,是否可以指定模式?