是否有一种方法使用SQL列出给定表的所有外键?我知道表名/模式,我可以把它插入。
当前回答
为了扩展Martin的精彩回答,这里有一个查询,它允许您根据父表进行过滤,并显示每个父表的子表的名称,以便您可以根据父表中的外键约束查看所有依赖的表/列。
select
con.constraint_name,
att2.attname as "child_column",
cl.relname as "parent_table",
att.attname as "parent_column",
con.child_table,
con.child_schema
from
(select
unnest(con1.conkey) as "parent",
unnest(con1.confkey) as "child",
con1.conname as constraint_name,
con1.confrelid,
con1.conrelid,
cl.relname as child_table,
ns.nspname as child_schema
from
pg_class cl
join pg_namespace ns on cl.relnamespace = ns.oid
join pg_constraint con1 on con1.conrelid = cl.oid
where con1.contype = 'f'
) con
join pg_attribute att on
att.attrelid = con.confrelid and att.attnum = con.child
join pg_class cl on
cl.oid = con.confrelid
join pg_attribute att2 on
att2.attrelid = con.conrelid and att2.attnum = con.parent
where cl.relname like '%parent_table%'
其他回答
这是我目前使用的,它将列出一个表,它的fkey约束[删除表子句,它将列出当前目录中的所有表]:
SELECT
current_schema() AS "schema",
current_catalog AS "database",
"pg_constraint".conrelid::regclass::text AS "primary_table_name",
"pg_constraint".confrelid::regclass::text AS "foreign_table_name",
(
string_to_array(
(
string_to_array(
pg_get_constraintdef("pg_constraint".oid),
'('
)
)[2],
')'
)
)[1] AS "foreign_column_name",
"pg_constraint".conindid::regclass::text AS "constraint_name",
TRIM((
string_to_array(
pg_get_constraintdef("pg_constraint".oid),
'('
)
)[1]) AS "constraint_type",
pg_get_constraintdef("pg_constraint".oid) AS "constraint_definition"
FROM pg_constraint AS "pg_constraint"
JOIN pg_namespace AS "pg_namespace" ON "pg_namespace".oid = "pg_constraint".connamespace
WHERE
--fkey and pkey constraints
"pg_constraint".contype IN ( 'f', 'p' )
AND
"pg_namespace".nspname = current_schema()
AND
"pg_constraint".conrelid::regclass::text IN ('whatever_table_name')
您可以通过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';
从最流行的答案改进查询
因为对于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'
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
使用Key所引用的主键的名称并查询information_schema:
select table_name, column_name
from information_schema.key_column_usage
where constraint_name IN (select constraint_name
from information_schema.referential_constraints
where unique_constraint_name = 'TABLE_NAME_pkey')
这里'TABLE_NAME_pkey'是外键引用的主键的名称。
推荐文章
- 将varchar字段的类型更改为整数:"不能自动转换为整数类型"
- PostgreSQL可以索引数组列吗?
- GROUP BY with MAX(DATE)
- 删除id与其他表不匹配的sql行
- 等价的限制和偏移SQL Server?
- PostgreSQL:角色不允许登录
- 为什么我不能在DELETE语句中使用别名?
- 在SQL Server Management Studio中保存带有标题的结果
- "where 1=1"语句
- 如何选择一个记录和更新它,与一个单一的查询集在Django?
- 如何查找Postgres / PostgreSQL表及其索引的磁盘大小
- 多语句表值函数vs内联表值函数
- 是使用各有一个模式的多个数据库更好,还是使用一个数据库有多个模式更好?
- 如何从Oracle的表中获取列名?
- NOLOCK提示在SELECT语句中的作用