ABOUT ME

-

Today
-
Yesterday
-
Total
-
  • [PostgresSQL] foreign key (constraint) 걸린 테이블 목록 확인
    DB/PostgreSQL 2017. 10. 18. 22:52

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

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

    댓글