constraints deferred

자주 헷갈리는 제약조건 지연에 대해 적어본다.

 

- 기능 : 기본적으로 제약조건이 걸린 컬럼에 대한 DML 수행시 한건씩 제약조건을 검사하며 제약조건 위반시 statement level rollback되는데

제약조건에 deferred를 설정하면 이를 한건씩이 아닌 commit 시에 트랜잭션 단위로 제약조건을 검사하고 제약조건 위반시 자동으로 트랜잭션을 rollback하는 기능

 

- 언제 쓰나? : 참조무결성 제약조건이 설정된 테이블들에 DML을 하려고 할 경우. 예를 들어 사원테이블의 부서번호를 60번에서 65번으로 바꿔야 하는데 부서테이블에는 60번만 존재할경우 부서테이블이나 사원테이블 어느 한군데서 update를 치면 FK 제약조건(걸려있으면)에 위배되게 된다. 이때 사원테이블의 FK를 deferred 시키고 진행하고 commit 하면 되겠다.

 

DEFERRABLE | NOT DEFERRABLE

- deferrable 과 not deferrable은 constraint(제약조건)를 지정(생성) 할때만 설정가능하며 not deferrable이 기본값이다.

- 애초에 deferrable이 설정 되어 있어야 그 아래 단계인 deferred와 immeidiate를 변경 가능하게 된다.

- 생성시 구문. 컬럼 레벨 테이블 레벨, alter 등 방법을 다 적기엔.. 밤이 늦었기에 alter table 기준으로 설명

 

SQL> create table dept2 as select * from departments;

 

SQL> alter table dept2

add constraint dept2_id_pk

primary key (department_id)

deferrable;

 

SQL> select status, deferrable, deferred
            from user_constraints where constraint_name ='DEPT2_ID_PK';

 

STATUS   DEFERRABLE     DEFERRED
-------- -------------- ---------
ENABLED  DEFERRABLE     IMMEDIATE

 

DEFERRED | IMMEDIATE

- 기본 값은 immediate이며, 만약 deferrable 상태가 not deferrable인데 deferred로 변경하면 아래와 같은 에러가 나온다.

 

SQL Error: ORA-02447: cannot defer a constraint that is not deferrable

 

에러 해설을 찾아보면

02447. 00000 -  "cannot defer a constraint that is not deferrable"
*Cause:    An attempt was made to defer a nondeferrable constraint
*Action:   Drop the constraint and create a new one that is deferrable

 

action에 제약조건을 drop하고 새로운걸 만들면서 deferrable로 설정하라고 한다.

 

위에서 말했듯이 deferrable 과 not deferrable은 constraint(제약조건)를 지정(생성) 할때만 설정가능하다는 것을 여기서도 알 수 있다.

 

- 방법. 제약조건 생성때 아예 아래처럼도 가능하다.

SQL> alter table dept2

add constraint dept2_id_pk

primary key (department_id)

deferrable initially deferred;

 

- 방법2 set constraints 사용. 이 방법이 가장 많이 쓰일 것이다.

SQL> set constraints dept2_id_pk deferred;

SQL> set constraints dept2_id_pk immediate;

 

- 방법3 alter session set constraints 사용. 그런데 테스트 해보니 결국 constraints의 속성에 따라 가기에 의미가 없어보인다. 아니면 약간 다른 용도로 사용하는 것도 같다.

SQL> alter session set constraints = immediate;

SQL> alter session set constraints = deferred;