如何列出表外键

Question

有没有办法使用 SQL 列出给定表的所有外键？我知道表名/模式，我可以将其插入。

原文由 smack0007 发布，翻译遵循 CC BY-SA 4.0 许可协议

Answer 1

您可以通过 information_schema 表执行此操作。例如：

 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 information_schema.constraint_column_usage AS ccu
      ON ccu.constraint_name = tc.constraint_name
      AND ccu.table_schema = tc.table_schema
WHERE tc.constraint_type = 'FOREIGN KEY' AND tc.table_name='mytable';

原文由 ollyc 发布，翻译遵循 CC BY-SA 4.0 许可协议

Answer 2

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

原文由 Sinisa Rudan 发布，翻译遵循 CC BY-SA 4.0 许可协议