是否有一种方法使用SQL列出给定表的所有外键?我知道表名/模式,我可以把它插入。
当前回答
最快的验证直接在bash答案完全基于这个答案
IFS='' read -r -d '' sql_code << EOF_SQL_CODE
SELECT
o.oid
, 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
, ROW_NUMBER () OVER (ORDER BY o.oid) as rowid
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 1=1
AND o.contype = 'f'
AND o.conrelid IN (SELECT oid FROM pg_class c WHERE c.relkind = 'r')
EOF_SQL_CODE
psql -d my_db -c "$sql_code"
其他回答
我升级了@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'
我认为你想要的和@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';
这将列出所有使用指定表作为外键的表
我自己的贡献。目标是备份所有外键的定义:
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
$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;
我写了一个解决方案,喜欢和经常使用。代码在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版本。如果需要的话,我预计会在未来几个月内对其进行更新。
莉丝
推荐文章
- 将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语句中的作用