Postgres:SQL列出表外键 [英] Postgres: SQL to list table foreign keys

问题描述

有没有一种方法可以使用SQL列出给定表的所有外键?我知道表名/架构，可以将其插入.

Is there a way using SQL to list all foreign keys for a given table? I know the table name / schema and I can plug that in.

推荐答案

您可以通过information_schema表来实现.例如:

You can do this via the information_schema tables. For example:

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';

这篇关于Postgres:SQL列出表外键的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！