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

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 라던지 제약조건을 작성하면 해당하는 결과가 떨어짐

+ Recent posts