Flashback Constraint Issue ;Reference Key가 걸린 table 삭제(부모테이블 삭제)후 flashback으로 복구 시 관련 constraint 살펴보기

시나리오
1. 
BCODE table 생성
code : primary key
name : not null

ACCOUNT table 생성
code : bcode(code) 참조
ac_number
opendate
2. 여러 자료들 입력
2-1. 제약조건 확인

3. bcode table 삭제
3-1. 제약조건 확인

4. flashback으로 bcode table 복구 후 제약조건 확인
5. 제약조건 조정 및 결론

1. 테이블 생성

SQL> create table bcode
  2  (code varchar(5) constraints bcode_code_pk PRIMARY KEY,
  3  name varchar(15) constraints bcode_name_nn not null);

SQL> create table account
  2  (code varchar(5) constraint account_code_fk references bcode(code),
  3  ac_number number(10) default 1234567890,
  4   opendate date default sysdate);

2. 여러 자료들 입력

SQL> insert into account(code) values('KB');
insert into account(code) values('KB')
*
ERROR at line 1:
ORA-02291: integrity constraint (KOO.ACCOUNT_CODE_FK) violated - parent key not found

SQL> insert into bcode values('KB', '국민');
SQL> insert into bcode values('WOO','우리');
SQL> insert into bcode values('HK','한국');
SQL> insert into bcode values('DG','대구');

SQL> commit;

Commit complete.


SQL> insert into account(code) values('KB');
SQL> insert into account(code) values('DG');

SQL> commit;

Commit complete.

2-1. 제약조건 확인

select constraint_name, constraint_type, r_constraint_name from user_constraints
where table_name in('BCODE','ACCOUNT');

CONSTRAINT_NAME      CO R_CONSTRAINT_NAME
-------------------- -- --------------------
ACCOUNT_CODE_FK      R  BCODE_CODE_PK
BCODE_NAME_NN        C
BCODE_CODE_PK        P

3. bcode table 삭제(부모테이블삭제)

SQL> drop table bcode;
drop table bcode
           *
ERROR at line 1:
ORA-02449: unique/primary keys in table referenced by foreign keys


SQL> drop table bcode cascade constraints;

Table dropped.

3-1. 제약조건 확인

SQL> select constraint_name, constraint_type, r_constraint_name from user_constraints
where table_name in('BCODE','ACCOUNT');

no rows selected

SQL> show recyclebin
ORIGINAL NAME    RECYCLEBIN NAME                OBJECT TYPE  DROP TIME
---------------- ------------------------------ ------------ -------------------
BCODE            BIN$uW8ZpsYstOngQAB/AQB/Kg==$0 TABLE        2012-02-21:09:00:35

4. flashback으로 bcode table 복구 후 제약조건 확인

SQL> flashback table bcode to before drop;

Flashback complete.

SQL> select constraint_name, constraint_type, r_constraint_name from user_constraints
where table_name in('BCODE','ACCOUNT');

CONSTRAINT_NAME      CO R_CONSTRAINT_NAME
-------------------- -- --------------------
BIN$uW8ZpsYptOngQAB/ C
AQB/Kg==$0

BIN$uW8ZpsYqtOngQAB/ P
AQB/Kg==$0

  1  select * from user_cons_columns
  2* where table_name in('BCODE', 'ACCOUNT')
SQL> /

OWNER CONSTRAINT_NAME      TABLE_NAME COLUMN_NAME                      POSITION
----- -------------------- ---------- ------------------------------ ----------
KOO   BIN$uW8ZpsYptOngQAB/ BCODE      NAME             
      AQB/Kg==$0

KOO   BIN$uW8ZpsYqtOngQAB/ BCODE      CODE                                    1
      AQB/Kg==$0
(NAME : NOT NULL로 추청, CODE : PRIMARY KEY로 추청)

5. 제약조건 조정 및 결론

SQL> alter table bcode rename constraint  "BIN$uW8ZpsYqtOngQAB/AQB/Kg==$0" to bcode_code_pk;

Table altered.

SQL> alter table bcode rename constraint "BIN$uW8ZpsYptOngQAB/AQB/Kg==$0" to bcode_name_nn;

Table altered.

SQL> select * from user_cons_columns where table_name in('BCODE', 'ACCOUNT');

OWNER CONSTRAINT_NAME      TABLE_NAME COLUMN_NAME                      POSITION
----- -------------------- ---------- ------------------------------ ----------
KOO   BCODE_CODE_PK        BCODE      CODE                                    1
KOO   BCODE_NAME_NN        BCODE      NAME

결론 : 복구를 해도 Foreign Key는 되살아나지 않습니다.
나머지 constraint는 되살아납니다(primary key, not null 등)
 

bcode(부모테이블)가 지워진 이후 account(자식테이블)에 여러 자료들이 삽입되었다면,

account의 code에는 있는 자료가 bcode의 code에는 없을 수도 있습니다.
(자식테이블쪽에는 존재하는 자료가 부모테이블에는 존재하지 않을 수 있습니다)

위와 같은 상황이 발생하면, 초기 기획의도와는 맞지않는 상황이 발생할 가능성이 매우 높습니다. 

이후 문제해결  http://gyh214.tistory.com/119 참조하세요