是否有一种方法使用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"
其他回答
在PostgreSQL提示符上发出\d+ tablename,除了显示表列的数据类型外,它还会显示索引和外键。
我自己的贡献。目标是备份所有外键的定义:
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
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
你可以使用PostgreSQL系统目录。也许您可以查询pg_constraint来请求外键。 您还可以使用信息模式
扩展到ollyc配方:
CREATE VIEW foreign_keys_view AS
SELECT
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';
然后:
SELECT * FROM foreign_keys_view WHERE table_name='YourTableNameHere';