database.sarang.net
UserID
Passwd
Database
DBMS
MySQL
ㆍPostgreSQL
Firebird
Oracle
Informix
Sybase
MS-SQL
DB2
Cache
CUBRID
LDAP
ALTIBASE
Tibero
DB 문서들
스터디
Community
공지사항
자유게시판
구인|구직
DSN 갤러리
도움주신분들
Admin
운영게시판
최근게시물
PostgreSQL Q&A 9916 게시물 읽기
No. 9916
부모자식테이블간 참조하는 FK컬럼 조회 쿼리
작성자
postgres
작성일
2017-11-20 19:04
조회수
6,926

 부모 자식 테이블간 참조하는 FK컬럼 조회하는 쿼리가 아래와 같이 있씁니다.

SELECT tc.table_name AS child_table,
kcu.column_name AS child_colum,
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_schema = 'pvs' and tc.table_name = 'tsd' 
 
pvs스키마에 있는 자식테이블인 tsd테이블이 어떤 부모테이블의 컬럼을 FK로 참조하는지 알수있는 쿼리인데 위 쿼리 결과로
 
 child_table | child_colum | foreign_table | foreign_column
---------------+-----------------+------------------+---------------------
 tsd            | lcsm_code   | tsh                  | hdate
 tsd            | hdate           | tsh                  | hdate
 tsd            | lcsm_code   | tsh                  | lcsm_code
 tsd            | hdate           | tsh                  | lcsm_code
(4 rows)
 
결과가 이렇게 나오는데 
실제 tsd(자식)테이블이 참조하는 tsh(부모)테이블의  컬럼은 
 
자식 테이블의 lcsm code컬럼이 부모 테이블의 lcsm code 컬럼
자식 테이블의 hdate 컬럼이 부모 테이블의 hdate 컬럼을
 
이렇게 참조하고있습니다. 즉, 위 쿼리 실행 결과의 2,3번째 row뿐이고 1,4번째 row는 없어야할 값입니다.
쿼리문에 약하다보니.. 위쿼리에서 어느부분을 수정해줘야 아래서 올바른 값을 결과로 뽑는지가 궁금합니다..
 
이 글에 대한 댓글이 총 1건 있습니다.

 information_schema.constraint_column_usage  이놈이 관건이었는데,

이 뷰에는 information_schema.key_column_usage 뷰의  position_in_unique_constraint 칼럼과 연결할 칼럼이 없는 것이 문제였네요.

information_schema 쪽의 표준규약이 그런 거라면 어쩔 수 없을 것 같습니다.

한 꼼수로 row_number() 윈도우 함수를 이용해서, information_schema.constraint_column_usage 뷰에서 information_schema.key_column_usage 뷰의  position_in_unique_constraint 칼럼과 연결할 칼럼값을 임시로 만들어 쓸 수 있을지도 모르겠지만,  information_schema.constraint_column_usage 뷰 정의를 봤을 때, 해당 row의 순서가 일정하리라는 보장이 없어 위험합니다.

 

원하는 정확한 작업을 하려면,

pg_catalog.pg_attribute, pg_catalog.pg_constraint 같은 pg_catalog 객체들로 풀어야 할 것 같네요.

저도 한 번도 안 만들어봐서 생각보다 복잡할 것 같네요.

pg_catalog.pg_constraint 테이블의 conkey, confkey 배열 처리를 깔끔하게 해야 원하는 결과가 나올 것 같네요.

잘 해결하시길 빕니다.

김상기(ioseph)님이 2017-11-20 22:22에 작성한 댓글입니다.
[Top]
No.
제목
작성자
작성일
조회
9919이상한 에러.. [4]
궁금
2017-11-22
8390
9918쿼리 기본 질문 [1]
postgres
2017-11-22
6309
9917테이블 truncate할때 FK로 묶인 부모,자식 테이블의 관계 [3]
postgres
2017-11-22
8040
9916부모자식테이블간 참조하는 FK컬럼 조회 쿼리 [1]
postgres
2017-11-20
6926
9915소스에서 돌리던 계산을 function에서 하려는데요 [5]
최대호
2017-11-20
6609
9914HA 질문드립니다 [4]
검콤이
2017-11-20
6445
9913postgresl에서 테이블 FK조회 [2]
postgres
2017-11-20
7143
Valid XHTML 1.0!
All about the DATABASE... Copyleft 1999-2023 DSN, All rights reserved.
작업시간: 0.048초, 이곳 서비스는
	PostgreSQL v16.1로 자료를 관리합니다