是否有一种方法使用SQL列出给定表的所有外键?我知道表名/模式,我可以把它插入。
当前回答
这个查询也适用于组合键:
select c.constraint_name
, x.table_schema as schema_name
, x.table_name
, x.column_name
, y.table_schema as foreign_schema_name
, y.table_name as foreign_table_name
, y.column_name as foreign_column_name
from information_schema.referential_constraints c
join information_schema.key_column_usage x
on x.constraint_name = c.constraint_name
join information_schema.key_column_usage y
on y.ordinal_position = x.position_in_unique_constraint
and y.constraint_name = c.unique_constraint_name
order by c.constraint_name, x.ordinal_position
其他回答
你可以使用PostgreSQL系统目录。也许您可以查询pg_constraint来请求外键。 您还可以使用信息模式
我创建了一个小工具来查询和比较数据库模式: Dump PostgreSQL数据库模式到文本
有关于FK的信息,但ollyc的回复提供了更多的细节。
$1 ('my_schema')是模式,$2 ('my_table')是表名:
SELECT ss.conname constraint_name, a.attname column_name, ss.refnamespace fk_table_schema, ss.reflname fk_table_name, af.attname fk_column_name
FROM pg_attribute a, pg_attribute af,
(SELECT r.oid roid, c.conname, rf.relname reflname, information_schema._pg_expandarray(c.conkey) x,
nrf.nspname refnamespace, rf.oid rfoid, information_schema._pg_expandarray(cf.confkey) xf
FROM pg_namespace nr, pg_class r, pg_constraint c,
pg_namespace nrf, pg_class rf, pg_constraint cf
WHERE nr.oid = r.relnamespace
AND r.oid = c.conrelid
AND rf.oid = cf.confrelid
AND c.conname = cf.conname
AND nrf.oid = rf.relnamespace
AND nr.nspname = $1
AND r.relname = $2) ss
WHERE ss.roid = a.attrelid AND a.attnum = (ss.x).x AND NOT a.attisdropped
AND ss.rfoid = af.attrelid AND af.attnum = (ss.xf).x AND NOT af.attisdropped
ORDER BY ss.conname, a.attname;
检查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');
我自己的贡献。目标是备份所有外键的定义:
SELECT
'ALTER TABLE ' || tc.table_schema || '.' || tc.table_name || E'\n
ADD FOREIGN KEY (' || kcu.column_name || ')' || E'\n
REFERENCES ' || ccu.table_schema || '.' || ccu.table_name ||
' (' || ccu.column_name || ') ' || E'\n ' ||
CASE WHEN rc.match_option <> 'NONE' THEN E'\n
MATCH ' || rc.match_option ELSE '' END ||
CASE WHEN rc.update_rule <> 'NO ACTION' THEN E'\n
ON UPDATE ' || rc.update_rule || ' ' ELSE '' END ||
CASE WHEN rc.delete_rule <> 'NO ACTION'
THEN 'ON DELETE ' || rc.delete_rule ELSE '' END || ';'
AS add_constraint
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
JOIN information_schema.referential_constraints AS rc
ON tc.constraint_name=rc.constraint_name
WHERE tc.constraint_type = 'FOREIGN KEY'
\t\a\g\a\ta
推荐文章
- 如何在Ruby On Rails中使用NuoDB手动执行SQL命令
- 查询JSON类型内的数组元素
- 确定记录是否存在的最快方法
- 获得PostgreSQL数据库中当前连接数的正确查询
- 在SQL选择语句Order By 1的目的是什么?
- 我如何循环通过一组记录在SQL Server?
- 纬度和经度的数据类型是什么?
- 如何在PostgreSQL中临时禁用触发器?
- 如何从命令行通过mysql运行一个查询?
- 外键约束可能导致循环或多条级联路径?
- 输入文件似乎是一个文本格式转储。请使用psql
- 使用LIMIT/OFFSET运行查询,还可以获得总行数
- 当恢复sql时,psql无效命令\N
- 货币应该使用哪种数据类型?
- 如何选择每一行的列值不是独特的