是否有一种方法使用SQL列出给定表的所有外键?我知道表名/模式,我可以把它插入。
当前回答
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
其他回答
SELECT
conrelid::regclass AS table_from,
conname,
pg_get_constraintdef(oid) as condef
FROM pg_catalog.pg_constraint r
也适用于所有约束条件。例如,使用pysql:
使用Key所引用的主键的名称并查询information_schema:
select table_name, column_name
from information_schema.key_column_usage
where constraint_name IN (select constraint_name
from information_schema.referential_constraints
where unique_constraint_name = 'TABLE_NAME_pkey')
这里'TABLE_NAME_pkey'是外键引用的主键的名称。
我认为你想要的和@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';
这将列出所有使用指定表作为外键的表
最快的验证直接在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"
检查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');
推荐文章
- 将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语句中的作用