是否有一种方法使用SQL列出给定表的所有外键?我知道表名/模式,我可以把它插入。
当前回答
另一种方式:
WITH foreign_keys AS (
SELECT
conname,
conrelid,
confrelid,
unnest(conkey) AS conkey,
unnest(confkey) AS confkey
FROM pg_constraint
WHERE contype = 'f' -- AND confrelid::regclass = 'your_table'::regclass
)
-- if confrelid, conname pair shows up more than once then it is multicolumn foreign key
SELECT fk.conname as constraint_name,
fk.confrelid::regclass as referenced_table, af.attname as pkcol,
fk.conrelid::regclass as referencing_table, a.attname as fkcol
FROM foreign_keys fk
JOIN pg_attribute af ON af.attnum = fk.confkey AND af.attrelid = fk.confrelid
JOIN pg_attribute a ON a.attnum = conkey AND a.attrelid = fk.conrelid
ORDER BY fk.confrelid, fk.conname
;
其他回答
您可以通过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';
检查ff帖子的解决方案,不要忘记标记这个,当你认为这是有帮助的
http://errorbank.blogspot.com/2011/03/list-all-foreign-keys-references-for.html
SELECT
o.conname AS constraint_name,
(SELECT nspname FROM pg_namespace WHERE oid=m.relnamespace) AS source_schema,
m.relname AS source_table,
(SELECT a.attname FROM pg_attribute a WHERE a.attrelid = m.oid AND a.attnum = o.conkey[1] AND a.attisdropped = false) AS source_column,
(SELECT nspname FROM pg_namespace WHERE oid=f.relnamespace) AS target_schema,
f.relname AS target_table,
(SELECT a.attname FROM pg_attribute a WHERE a.attrelid = f.oid AND a.attnum = o.confkey[1] AND a.attisdropped = false) AS target_column
FROM
pg_constraint o LEFT JOIN pg_class f ON f.oid = o.confrelid LEFT JOIN pg_class m ON m.oid = o.conrelid
WHERE
o.contype = 'f' AND o.conrelid IN (SELECT oid FROM pg_class c WHERE c.relkind = 'r');
我认为你想要的和@ollyc写的很接近的是:
SELECT
tc.constraint_name, tc.table_name, kcu.column_name,
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
JOIN information_schema.constraint_column_usage AS ccu
ON ccu.constraint_name = tc.constraint_name
WHERE constraint_type = 'FOREIGN KEY' AND ccu.table_name='YourTableNameHere';
这将列出所有使用指定表作为外键的表
这是我目前使用的,它将列出一个表,它的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')
SELECT conrelid::regclass AS table_name,
conname AS foreign_key,
pg_get_constraintdef(oid)
FROM pg_constraint
WHERE contype = 'f'
AND connamespace = 'public'::regnamespace
ORDER BY conrelid::regclass::text, contype DESC;
https://soft-builder.com/how-to-list-all-foreign-keys-in-postgresql-database
推荐文章
- 如何在Ruby On Rails中使用NuoDB手动执行SQL命令
- 查询JSON类型内的数组元素
- 确定记录是否存在的最快方法
- 获得PostgreSQL数据库中当前连接数的正确查询
- 在SQL选择语句Order By 1的目的是什么?
- 我如何循环通过一组记录在SQL Server?
- 纬度和经度的数据类型是什么?
- 如何在PostgreSQL中临时禁用触发器?
- 如何从命令行通过mysql运行一个查询?
- 外键约束可能导致循环或多条级联路径?
- 输入文件似乎是一个文本格式转储。请使用psql
- 使用LIMIT/OFFSET运行查询,还可以获得总行数
- 当恢复sql时,psql无效命令\N
- 货币应该使用哪种数据类型?
- 如何选择每一行的列值不是独特的