[Postgresql] 외래키 참조 테이블 조회 방법

주어진 테이블이 참조되는 테이블인 경우의 외래 키 관계를 찾는 법

 

SELECT 
    CCU.table_name AS search_table_name,
    CCU.column_name AS search_column_name,
    KCU.table_name AS foreign_table_name,
    KCU.column_name AS foreign_column_name,
    KCU.constraint_name AS foreign_constraint_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 
    TC.constraint_type = 'FOREIGN KEY'
    AND CCU.table_name = "테이블 명";

 

 

주어진 테이블이 외래 키를 참조하는 테이블인 경우의 외래 키 관계를 찾는 법

SELECT 
    TC.table_name AS search_table_name,
    KCU.column_name AS search_column_name,
    CCU.table_name AS foreign_table_name,
    CCU.column_name AS foreign_column_name,
    CCU.constraint_name AS foreign_constraint_name
FROM 
    information_schema.table_constraints AS TC
    JOIN information_schema.key_column_usage AS KCU ON KCU.constraint_name = TC.constraint_name
    JOIN information_schema.constraint_column_usage AS CCU ON CCU.constraint_name = TC.constraint_name
WHERE 
    TC.constraint_type = 'FOREIGN KEY'
    AND TC.table_name = "테이블 명";