오라클 FK 생성 스크립트 추출하기

 

오라클 딕셔너리 정보를 기반으로 해당 소유자 혹은 테이블의 FK생성 스크립트를 추출해주는 스크립트를 공개합니다.

 

전체 테이블의 FK를 재생성 한다던가 테이블의 컬럼의 순서변경 , 컬럼추가시 테이블 재생성을 위한 관련 FK스크립트

 

추출시에 사용하시면 좋을것 같습니다.

 

 

 

 

테스트 부모 테이블 생성

--부모 테이블 생성
CREATE TABLE TMP_P AS
SELECT '11' ID , 'SAMSUNG' NAME FROM DUAL;

--PK제약조건 색성
ALTER TABLE TMP_P ADD CONSTRAINT TMP_P_PK PRIMARY KEY(ID)
    

테스트 자식 테이블 생성

--자식 테이블 생성
CREATE TABLE TMP_P AS
SELECT '11' ID , 'SAMSUNG' NAME FROM DUAL;

--자식테이블 FK생성
ALTER TABLE TMP_C ADD
(
	CONSTRAINT TMP_C_FK1
  FOREIGN KEY (P_ID)
  REFERENCES TMP_P(ID)
);

생성 FK확인

--생성된 FK확인
SELECT * FROM DBA_CONSTRAINTS WHERE TABLE_NAME='TMP_C'

수행결과

OWNER|CONSTRAINT_NAME|CONSTRAINT_TYPE|TABLE_NAME|SEARCH_CONDITION|R_OWNER|R_CONSTRAINT_NAME|DELETE_RULE|STATUS |DEFERRABLE    |DEFERRED |VALIDATED|GENERATED|BAD|RELY|LAST_CHANGE        |INDEX_OWNER|INDEX_NAME|INVALID|VIEW_RELATED|
-----|---------------|---------------|----------|----------------|-------|-----------------|-----------|-------|--------------|---------|---------|---------|---|----|-------------------|-----------|----------|-------|------------|
SYS  |TMP_C_FK1      |R              |TMP_C     |                |SYS    |TMP_P_PK         |NO ACTION  |ENABLED|NOT DEFERRABLE|IMMEDIATE|VALIDATED|USER NAME|   |    |2020-02-28 11:25:30|           |          |       |            |

 

 

 

FK생성 스크립트 추출

WITH TMP AS(
SELECT 'SYS' FROM_OWNER, 'SYS' TO_OWNER FROM DUAL
)
SELECT 'ALTER TABLE ' || (SELECT TO_OWNER FROM TMP) || '.' || TABLE_NAME || ' ADD
(
	CONSTRAINT ' || CONSTRAINT_NAME || '
  FOREIGN KEY (' || (SELECT LISTAGG(COLUMN_NAME,',') WITHIN GROUP (ORDER BY POSITION) FROM DBA_CONS_COLUMNS WHERE CONSTRAINT_NAME=A.CONSTRAINT_NAME AND OWNER=(SELECT FROM_OWNER FROM TMP)) || ')
  REFERENCES ' || (SELECT TO_OWNER FROM TMP) || '.' || (SELECT TABLE_NAME FROM DBA_CONSTRAINTS WHERE OWNER=(SELECT FROM_OWNER FROM TMP) AND CONSTRAINT_NAME=TRIM(A.R_CONSTRAINT_NAME)) 
               || '(' || (SELECT LISTAGG(COLUMN_NAME,',') WITHIN GROUP (ORDER BY POSITION) FROM DBA_CONS_COLUMNS WHERE OWNER=(SELECT FROM_OWNER FROM TMP) AND CONSTRAINT_NAME=A.R_CONSTRAINT_NAME)  || ')
);
' FK_ADD_SCRIPT
FROM DBA_CONSTRAINTS A WHERE OWNER=(SELECT FROM_OWNER FROM TMP) AND CONSTRAINT_TYPE='R' AND TABLE_NAME='TMP_C'

수행결과

ALTER TABLE SYS.TMP_C ADD
(
	CONSTRAINT TMP_C_FK1
  FOREIGN KEY (P_ID)
  REFERENCES SYS.TMP_P(ID)
);