[PostgresSQL] foreign key (constraint) 걸린 테이블 목록 확인

검색하고자 하는 테이블을 참조하고 있는 테이블 및 컬럼 리스트 확인

SELECT kcu.table_name AS child_table, kcu.table_schema AS child_schema, kcu.column_name AS child_column, kcu.constraint_name AS child_constraint FROM information_schema.table_constraints tc JOIN information_schema.key_column_usage kcu ON tc.constraint_name = kcu.constraint_name JOIN information_schema.constraint_column_usage ccu ON ccu.constraint_name = tc.constraint_name WHERE tc.constraint_type = 'FOREIGN KEY' and ccu.table_name = '테이블명'; GROUP BY kcu.table_name, kcu.table_schema, kcu.column_name, kcu.constraint_name;

현재 테이블이 참조하고 있는 테이블 및 컬럼 확인

SELECT tc.table_name AS child_table, kcu.column_name AS child_column, ccu.table_name AS foreign_table, ccu.column_name AS foreign_column FROM information_schema.table_constraints tc JOIN information_schema.key_column_usage kcu ON tc.constraint_name::text = kcu.constraint_name::text JOIN information_schema.constraint_column_usage ccu ON ccu.constraint_name::text = tc.constraint_name::text WHERE tc.constraint_type = 'FOREIGN KEY' and tc.table_name = '테이블명';

위의 FOREIGN KEY 대신 PRIMARY KEY 라던지 제약조건을 작성하면 해당하는 결과가 떨어짐