是否有一种方法使用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%'
其他回答
我写了一个解决方案,喜欢和经常使用。代码在http://code.google.com/p/pgutils/。看这些小疙瘩。foreign_keys视图。
不幸的是,输出太冗长,这里不包括。但是,你可以在数据库的公共版本上尝试一下,就像这样:
$ psql -h unison-db.org -U PUBLIC -d unison -c 'select * from pgutils.foreign_keys;
这至少适用于8.3版本。如果需要的话,我预计会在未来几个月内对其进行更新。
莉丝
检查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 * from information_schema.key_column_usage
where constraint_catalog=current_catalog and table_name='your_table_name'
and position_in_unique_constraint notnull;
我自己的贡献。目标是备份所有外键的定义:
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
我升级了@ollyc的答案,目前在顶部。 我同意@fionbio,因为key_column_usage和constraint_column_usage在列级上没有相关信息。
如果constraint_column_usage具有像key_column_usage一样的ordinal_position列,则可以将其与该列连接。所以我做了一个ordinal_position到constraint_column_usage如下所示。
我无法确认手动创建的ordinal_position与key_column_usage的顺序完全相同。但我检查了一下,至少在我的箱子里是完全一样的顺序。
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 (select row_number() over (partition by table_schema, table_name, constraint_name order by row_num) ordinal_position,
table_schema, table_name, column_name, constraint_name
from (select row_number() over (order by 1) row_num, table_schema, table_name, column_name, constraint_name
from information_schema.constraint_column_usage
) t
) AS ccu
ON ccu.constraint_name = tc.constraint_name
AND ccu.table_schema = tc.table_schema
AND ccu.ordinal_position = kcu.ordinal_position
WHERE tc.constraint_type = 'FOREIGN KEY' AND tc.table_name = 'mytable'