是否有一种方法使用SQL列出给定表的所有外键?我知道表名/模式,我可以把它插入。
当前回答
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提示符上发出\d+ tablename,除了显示表列的数据类型外,它还会显示索引和外键。
只需替换'您的表名'在下面的查询与您的表名。
简短但贴心的赞,如果对你有用的话。
select * from information_schema.key_column_usage
where constraint_catalog=current_catalog and table_name='your_table_name'
and position_in_unique_constraint notnull;
这是我目前使用的,它将列出一个表,它的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')
从最流行的答案改进查询
因为对于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'
SELECT r.conname
,ct.table_name
,pg_catalog.pg_get_constraintdef(r.oid, true) as condef
FROM pg_catalog.pg_constraint r, information_schema.constraint_table_usage ct
WHERE r.contype = 'f'
AND r.conname = ct.constraint_name
ORDER BY 1
推荐文章
- 使用SQL查询查找最近的纬度/经度
- 将一列的多个结果行连接为一列,按另一列分组
- 检查MySQL表是否存在而不使用“select from”语法?
- 使用pgadmin连接到heroku数据库
- 在PostgreSQL中快速发现表的行数
- 更改varchar列的大小为较低的长度
- 从表中选择1是什么意思?
- 如何首次配置postgresql ?
- SQL Server中User和Login的区别
- 数据库性能调优有哪些资源?
- 如何更改表的默认排序规则?
- 为两列的组合添加唯一的约束
- 如何在PostgreSQL中自动更新时间戳
- 设置NOW()为datetime数据类型的默认值?
- 在MySQL中Datetime等于或大于今天