[oracle] 기본키와 연결된 테이블을 찾는 방법.

-오라클 테이블([TABLE_NAME])과 연결되어 있는 테이블/제약조건/상태를 조회하는 쿼리.

SELECT
    A.TABLE_NAME TABLE_NAME,
    A.CONSTRAINT_NAME KEY_NAME,
    B.TABLE_NAME REFERENCING_TABLE,
    (SELECT COMMENTS FROM USER_TAB_COMMENTS WHERE TABLE_NAME = B.TABLE_NAME) TABLE_NM,
    B.CONSTRAINT_NAME FOREIGN_KEY_NAME,
    B.STATUS FK_STATUS
FROM USER_CONSTRAINTS A, USER_CONSTRAINTS B
WHERE
    A.CONSTRAINT_NAME = B.R_CONSTRAINT_NAME AND
    B.CONSTRAINT_TYPE = 'R'
    AND A.TABLE_NAME = [TABLE_NAME]
ORDER BY 1, 2, 3, 4;

---

-쿼리 실행 예시.

1.특정 테이블('ALBATAPPL')의 제약조건 & 기본키 조회.

SELECT * FROM ALL_CONS_COLUMNS
WHERE CONSTRAINT_NAME = 
(SELECT CONSTRAINT_NAME FROM ALL_CONSTRAINTS WHERE TABLE_NAME = 'ALBATAPPL' AND CONSTRAINT_TYPE = 'P' )

---

2.특정 테이블('ALBATAPPL')의 기본키를 참조하는 테이블 & 제약조건 & 상태를 조회.

SELECT
    A.TABLE_NAME TABLE_NAME,
    A.CONSTRAINT_NAME KEY_NAME,
    B.TABLE_NAME REFERENCING_TABLE,
    (SELECT COMMENTS FROM USER_TAB_COMMENTS WHERE TABLE_NAME = B.TABLE_NAME) TABLE_NM,
    B.CONSTRAINT_NAME FOREIGN_KEY_NAME,
    B.STATUS FK_STATUS
  FROM USER_CONSTRAINTS A, USER_CONSTRAINTS B
  WHERE
    A.CONSTRAINT_NAME = B.R_CONSTRAINT_NAME AND
    B.CONSTRAINT_TYPE = 'R'
    AND A.TABLE_NAME = 'ALBATAPPL'
  ORDER BY 1, 2, 3, 4;