[SQL] INSERT ALL 쿼리로 부모테이블과 자식테이블 적재 테스트(FK)

[테스트 환경] 

  - Windows Server 2008 64bit 

  - Oracle 11g 64bit standard 

1. 문제 상황 

  - 시퀀스를 사용해서 INSERT ALL로 부모 테이블과 자식 테이블에 데이터를 적재한다. 

1.1. 테이블 생성 스크립트

-- 부모테이블
CREATE TABLE ADB_PARENTS (
 PR_SEQNO   NUMBER(9)    NOT NULL, 
 PARENTS_NM VARCHAR2(50) NULL     
);

ALTER TABLE ADB_PARENTS
 ADD CONSTRAINT PK_ADB_PARENTS1 
  PRIMARY KEY (
   PR_SEQNO 
  );

-- 자식테이블
CREATE TABLE ADB_CHILD (
 PR_SEQNO NUMBER(9)          NOT NULL,
 CHILD_NM VARCHAR2(50)       NULL      
);

ALTER TABLE ADB_CHILD
 ADD CONSTRAINT PK_ADB_CHILD 
  PRIMARY KEY (
   PR_SEQNO
  );

ALTER TABLE ADB_CHILD
 ADD CONSTRAINT ADB_CHILD_FK 
  FOREIGN KEY (
   PR_SEQNO
  )
  REFERENCES ADB_PARENTS ( 
   PR_SEQNO 
  );

1.2. 시퀀스 생성 스크립트

CREATE SEQUENCE PR_SEQNO
  START WITH 1
  MAXVALUE 999999999
  MINVALUE 1
  NOCYCLE
  NOCACHE
  NOORDER;

1.3. 함수 생성 스크립트 

  - 하나의 시퀀스 번호로 부모 테이블과 자식 테이블에 데이터를 적재해야하기 때문에 함수를 이용해서 채번한다.

CREATE OR REPLACE FUNCTION FN_ADB_SEQ(IN_VAL_CHK IN VARCHAR2) RETURN NUMBER AS
    V_SEQ NUMBER;
BEGIN

    IF IN_VAL_CHK = 'PR_SEQNO' THEN
       SELECT PR_SEQNO.NEXTVAL
       INTO V_SEQ
       FROM DUAL;
    END IF;
 
RETURN V_SEQ;

END;
/

1.4. 데이터 적재

INSERT ALL
WHEN NO = 1 THEN
INTO ADB_PARENTS(PR_SEQNO,PARENTS_NM) VALUES (PR_SEQNO, NM)
WHEN NO = 2 THEN
INTO ADB_CHILD(PR_SEQNO,CHILD_NM) VALUES (PR_SEQNO, NM)
SELECT    A.NO, A.PR_SEQNO, A.NM
FROM      (SELECT    B.NO
                    ,A.PR_SEQNO
                    ,CASE
                       WHEN B.NO = 1 THEN '부모테이블' || A.PR_SEQNO
                       WHEN B.NO = 2 THEN '자식테이블' || A.PR_SEQNO
                     END
                       AS NM
           FROM      (SELECT /*+ NO_MERGE */
                            FN_ADB_SEQ('PR_SEQNO') AS PR_SEQNO
                      FROM      DUAL
                      CONNECT BY LEVEL <= 1000) A
                    ,(SELECT    ROWNUM AS NO
                      FROM      DUAL
                      CONNECT BY LEVEL <= 2) B
           ORDER BY  B.NO) A
ORDER BY  A.NO;

1.5. 에러 발생 확인

ORA-02291: 무결성 제약조건(ADB_CHILD_FK)이 위배되었습니다- 부모 키가 없습니다

2. 문제 원인 파악

  - ORDER BY 정렬 순으로 데이터가 적재되지 않는 것을 확인 

    - 각 테이블에 적재된 시간을 알기 위해서 트리거를 사용한다.

2.1. 테이블 생성 스크립트

CREATE TABLE ADB_PARENTS_CK(PR_SEQNO NUMBER, REG_DTTM TIMESTAMP DEFAULT SYSTIMESTAMP NOT NULL);

CREATE TABLE ADB_CHILD_CK(PR_SEQNO NUMBER, REG_DTTM TIMESTAMP DEFAULT SYSTIMESTAMP NOT NULL);

2.2. 트리거 생성 스크립트

  - 부모 테이블과 자식 테이블에 데이터를 적재하기 전에 트리거로 2.1.에서 생성한 테이블에 적재 시간을 기록한다.

CREATE OR REPLACE TRIGGER TR_ADB_PARENTS
BEFORE INSERT
ON ADB_PARENTS REFERENCING NEW AS New OLD AS Old
FOR EACH ROW
DECLARE
  PRAGMA AUTONOMOUS_TRANSACTION;
  V_PR_SEQNO NUMBER;
BEGIN
  V_PR_SEQNO := :NEW.PR_SEQNO ;
  INSERT INTO   ADB_PARENTS_CK (PR_SEQNO) VALUES (V_PR_SEQNO);
  COMMIT;  
END TR_ADB_PARENTS;
/

CREATE OR REPLACE TRIGGER TR_ADB_CHILD
BEFORE INSERT
ON  ADB_CHILD REFERENCING NEW AS New OLD AS Old
FOR EACH ROW
DECLARE
  PRAGMA AUTONOMOUS_TRANSACTION;
  V_PR_SEQNO NUMBER;
BEGIN
  V_PR_SEQNO := :NEW.PR_SEQNO ;
  INSERT INTO   ADB_CHILD_CK (PR_SEQNO) VALUES (V_PR_SEQNO);
  COMMIT;  
END TR_ADB_CHILD;
/

2.3. 테이블 제약조건 제거

ALTER TABLE ADB_CHILD DROP CONSTRAINT ADB_CHILD_FK;

2.4. 데이터 적재

INSERT ALL
WHEN NO = 1 THEN
INTO ADB_PARENTS(PR_SEQNO,PARENTS_NM) VALUES (PR_SEQNO, NM)
WHEN NO = 2 THEN
INTO ADB_CHILD(PR_SEQNO,CHILD_NM) VALUES (PR_SEQNO, NM)
SELECT    A.NO, A.PR_SEQNO, A.NM
FROM      (SELECT    B.NO
                    ,A.PR_SEQNO
                    ,CASE
                       WHEN B.NO = 1 THEN '부모테이블' || A.PR_SEQNO
                       WHEN B.NO = 2 THEN '자식테이블' || A.PR_SEQNO
                     END
                       AS NM
           FROM      (SELECT /*+ NO_MERGE */
                            FN_ADB_SEQ('PR_SEQNO') AS PR_SEQNO
                      FROM      DUAL
                      CONNECT BY LEVEL <= 1000) A
                    ,(SELECT    ROWNUM AS NO
                      FROM      DUAL
                      CONNECT BY LEVEL <= 2) B
           ORDER BY  B.NO) A
ORDER BY  A.NO;

COMMIT;

2.5. 결과
  - ORDER BY 절의 순서대로 데이터가 적재되지 않고 자식 테이블에 데이터가 먼저 적재된 것을 확인할 수 있다.

SELECT    A.PR_SEQNO, A.REG_DTTM AS PARENTS_REG_DTTM, B.REG_DTTM AS CHILD_REG_DTTM
FROM      ADB_PARENTS_CK A
         ,ADB_CHILD_CK B
WHERE     B.PR_SEQNO = A.PR_SEQNO;

3. 결론 

  - SELECT 절의 ORDER BY가 적재 순서를 보장하지 않는다. 

    따라서 INSERT ALL로 부모 테이블과 자식 테이블에 데이터를 적재할 때 FK 제약조건이 제거되어야 한다.