Constraints의 활성화 및 비활성화

1. Constraints 상태


Constraints의 상태는 4종류가 있다. Table 생성시 constraint의 기본값은 enable이다.
하지만, 데이터베이스의 작업 상황에 따라 constraint를 disable 시킬 필요도 있다.

1) Disable Novalidate

- 비활성화 상태로 constraint가 생성되었지만 현재는 작동하지 않는 상태. 즉, 입력된 데이터가 constraint에 위배 될수도 있는 상태

2) Disable Validate

- constraint가 있는 컬럽을 수정할 수 없는 상태이며 constraint의 index는 삭제되고 constraint는 비활성화 된다.

3) Enable Novalidate

- 활성화 상태로 비활성화 상태에서 데이터가 이미 저장되었을 경우, constraint를 적용한다. 이때 constraint에 위배되면 에러를 발생하고 fail 된다.
enabled validate 이후는 constraint에 적합한 데이터만 입력된다.

4) Enable Validate

- validate와 달리 비활성화 상태에서 데이터가 이미 저장되었을 경우, constraint를 적용하지 않는다. 결국 저장된 데이터 중 constraint에 윕배된 데이터가 존재하더라도 무시한다.
enabled novalidate 이후에는 constraint에 적합한 데이터만 입력된다.

 

2. Constraint 상태 변경 방법

1) Disable Novalidate, Disable Validate

Alter Table 테이블명 Disable {Novalidate | Validate} constraint명 [Cascade];

2) Enable Novalidate

Alter Table 테이블명 Enable Novalidate {Constraint constraint명 | Primary key | Unique(컬럼명)}

3) Enable Validate

Alter Table 테이블명 Enable [Validate] {Constraint constraint명 | Primary key | Unique(컬럼명)}
[Using Index index storage]
[Exceptions Into schema.Execptions];

3. Constraint 상태 변경 실습

실습을 위해 t1 테이블을 생성 하고 col1에는 unique, col2에는 not null 제약조건을 생성해 준다.

SQL> create table t1
  2  (col1 number constraint t1_col1_uk unique,
  3  col2 number constraint t1_col2_nn not null);

테이블을 생성한 후 value 값을 넣어준다.

SQL> insert into t1 values (1,1);

1 row created.

SQL> insert into t1 values (2,2);

1 row created.

SQL> insert into t1 values (3,3);

1 row created.

테이블 생성시 constraint의 기본값은 Enable validate로 Disable Novalidate로 변경하여 constraint를 비활성화 시킨다.

SQL> alter table t1 disable novalidate constraint t1_col1_uk;

Table altered.

변경 후 constraint에 위배되는 value값을 입력해 보자.

SQL> insert into t1 values (1,1);

1 row created.

SQL> insert into t1 values (1,1);

1 row created.

constraint가 비활성화 상태로 constraint에 위배되는 데이터가 입력됨을 확인할 수 있다.

SQL> select * from t1;

      COL1       COL2
---------- ----------
         1          1
         2          2
         3          3
         1          1
         1          1

Table created.

다시 t1_col1_uk 제약을 enable validate 상태로 변경하려고 하면 입력된 값이 제약조건에 위배되기 때문에 에러가 발생하며 변경되지 않는다.

SQL> alter table t1 enable validate constraint t1_col1_uk;
alter table t1 enable validate constraint t1_col1_uk
*
ERROR at line 1:
ORA-02299: cannot validate (SYS.T1_COL1_UK) - duplicate keys found

현재 constraint에 위배된 데이터는 그대로 남겨두고 앞으로 입력될 데이터는 constraint 가 적용되도록 enable novalidate 상태로 변경한다. 이때 PK 혹은 UK 제약조건일 경우 해당 컬럼에 반드시 인덱스가 있어야 하므로 index를 생성한 후 변경한다.

SQL> create index t1_col1_idx on t1(col1);

Index created.

SQL> alter table t1 enable novalidate constraint t1_col1_uk;

Table altered.

SQL> select * from t1;

      COL1       COL2
---------- ----------
         1          1
         2          2
         3          3
         1          1
         1          1

이제 제약조건을 enable validate로 변경하려고 하면 입력된 값중 제약조건에 위배되는 값을 찾아 수정하거나 삭제해 주어야 변경이 가능하다.
현재의 t1 테이블은 컬럼이 적어 쉽게 확인 및 수정이 가능하지만, 많은 경우에는 제약조건에 위배되는 테이블만을 찾아 삭제하기가 쉽지는 않을 것이다.
EXCEPTIONS TABLE을 활용하면 제약조건에 위배되는 컬럼만을 찾아 수정이 가능하다.

※ EXCEPTIONS TABLE을 이용하여 중복된 자료 확인.

Enable Validate로 상태를 변경하려고 할 때 예외 사항이 없다면 enable이 되겠지만 위와 같이 unique 제약사항에 위배되는 데이터가 table에 포함되어 있다면 다음의 error와 함께 disable인 상태로 남게 된다.

SQL> alter table t1 enable validate constraint t1_col1_uk exceptions into exceptions;
alter table t1 enable validate constraint t1_col1_uk exceptions into exceptions
*
ERROR at line 1:
ORA-02299: cannot validate (SYS.T1_COL1_UK) - duplicate keys found

이 예외 사항에 대한 정보는 exceptions라는 table에 들어가게 된다.
exceptions table에는 constraint를 enable validate 상태로 변경시키는 대상 테이블에서 에러를 유발시키는 row들의 rowid, 대상 테이블의 소유자, 대상 테이블 명, 에러를 유발시키는 constraint 의 정보를 담고 있다.

enable을 하기 전에 enable의 exceptions options으로부터 정보를 수용하려면 적합한 예외 사항 보고 table인 exceptions라는 table을 생성하여야 한다.

이것은 $ORACLE_HOME/rdbms/admin/utlexcpt.sql을 해당 user에서 실행해주면 생성된다.
이 script 내에서 table의 이름을 변경하여 사용하여도 가능하다.

SQL> !more $ORACLE_HOME/rdbms/admin/utlexcpt.sql   -- 스크립트 파일 내용 확인
SQL> @$ORACLE_HOME/rdbms/admin/utlexcpt.sql

아래와 같이 중복된 record에 대한 정보를 exceptions table에서 확인가능하다.

SQL> col owner format a10
SQL> col table_name format a10
SQL> col constraint format a20
SQL> select * from exceptions
  2  ;

ROW_ID             OWNER      TABLE_NAME CONSTRAINT
------------------ ---------- ---------- --------------------
AAACxMAABAAALHEAAE SYS        T1         T1_COL1_UK
AAACxMAABAAALHEAAD SYS        T1         T1_COL1_UK
AAACxMAABAAALHEAAA SYS        T1         T1_COL1_UK

exceptions table 내에는 ROW_ID, OWNER, TABLE_NAME, CONSTRAINT 정보만 출력되기 때문에 제약조건에 위배된 컬럼을 확인하기 위해 아래와 같이 자세한 sql을 이용하여 제약 조건에 위배되는 컬럼을 확인할 수 있다.
 

SQL> select rowid, t1.* from t1 where rowid in (select row_id from exceptions);

ROWID                    COL1       COL2
------------------ ---------- ----------
AAACxMAABAAALHEAAE          1          1
AAACxMAABAAALHEAAD          1          1
AAACxMAABAAALHEAAA          1          1

해당 data를 확인한 후 제약조건에 위배되는 자료를 삭제 하거나 변경하여야 한다.
제약조건에 위배되는 컬럼의 값을 update로 변경하자.
 

SQL> update t1 set col1 = 101 where rowid = 'AAACxMAABAAALHEAAD';

1 row updated.

SQL> update t1 set col1 = 102 where rowid = 'AAACxMAABAAALHEAAA';

1 row updated.

변경 후 t1_col1_uk constraint를 다시 enable validate로 변경 한다.

SQL> alter table t1 enable validate constraint t1_col1_uk exceptions into exceptions;

Table altered.

이제 t1_col1_uk 제약조건이 enable validate 상태로 변경이 완료 되었다.


참조 : http://radiocom.kunsan.ac.kr/lecture/oracle/what_is/constraint_enable_disable.html