2012.01.27 Admin 8일차(1) (Constraints)

SQL에서 제약조건(constraints)관련 글을 보세요
( http://gyh214.tistory.com/62 )

*Migration 설명과 마이그레이션시 주의사항(Constraint관련해서)

마이그레이션시 생각해볼 문제점 : 제약조건

AS-IS의 한 테이블이 1억행을 가지고 있는데, 그 중 한 컬럼이 NOT NULL CONSTRAINT가 설정되어 있습니다..
이 컬럼의 값들은 보나마나 NULL인 값들이 없을 것입니다.(제약조건이 걸린채로 이때까지 데이터들이 입력되었으므로)

이럴때 TO-BE에 마이그래이션시 같은 테이블에 자료를 넣을때 NOT NULL제약조건이 필요가 있을까요?
괜히 NOT NULL제약조건이 있으면 import해서 자료가 넘어갈때마다 TO-BE쪽 테이블에서 제약조건을 검사하기때문에 속도가 느립니다.

이럴때는 TO-BE쪽 테이블의 제약조건을 잠시 해제하고 자료를 전부 마이그래이션 한 후 다시 해제한 제약조건을 enable하면 빠른 속도로 마이그래이션작업을 할 수 있습니다.
(요약 : 제약조건 풀어놓고 DATA 부어놓고(migration하고) 나중에 제약조건 다시 SET)

이 포스팅의 목적
Constraint 설정방법알기, 올바른 constraint의 상태사용하기

Constraints State(제약조건의 상태) ***
(validate : 입증하다, 승인하다, 타당한)
1. DISABLE NOVALIDATE : 'constraint가 없다' 와 동일

2. DISABLE VALIDATE : 이 상태로 테이블에 SET해 놓으면 그 테이블은 읽기전용테이블이 됩니다.

3. ENABLE NOVALIDATE : 자료가 insert될때 기존의 data들은 검증하지 않고 들어오는 data만 해당 constraint에 적합한가 검사합니다.(들어오는것만 신경씀)

4. ENABLE VALIDATE : 자료가 insert될때 기존의 data들이 해당 constraint에 적합한가 검사하고, 들어오는 data들도 해당 constraint에 적합한지 검사합니다.(기존의 것, 들어오는것 다 신경 씀)
*여기서 constraint disable 시 디폴트는 DISABLE NOVALIDATE, constraint enable시 디폴트는 ENABLE VALIDATE입니다.



Constraint검증하는 시기 (Defining Constraints)
1. Immediate(Default) : 들어올때마다 검증

2. Deferred : commit까지 기다린 후 한방에 검증
*회원가입사이트 생각하시면, 전부 입력하고 완료 누르고 나서 오류뜨는것(Deffered), 칸 넘어갈때마다 오류뜨는것(Immediate)


뜬금없는 참조글 :
Reference Key 삭제 시 해당 칼럼을 참조하는 Foreign Key가 있으므로 삭제가 불가능 할 때에는 cascade옵션을 줘서 같이 삭제


제약조건 추가하면서 테이블 만들기

CREATE TABLE scott.test_koo(
id NUMBER(5)
	CONSTRAINT test_koo_id_pk PRIMARY KEY
	DEFERRABLE
	USING INDEX
	TABLESPACE indx,
name varchar2(20)
	CONSTRAINT test_koo_name_nn NOT NULL)
tablespace koo;
 

 ** Primary Key, Unique 를 생성할때는 oracle에서 무조건 해당 컬럼에 대한 index를 만들어줍니다.
이 때 테이블과 index는 다른 tablespace에 있는게 권장되기 떄문에 중간에 USING INDEX ~ TABLESPACE indx 를 사용.
* Deferrable : commit떄 한꺼번에 적합성을 검사한다는 의미
* 테이블 생성시 필요한 tablespace들은 미리 생성이 되어있어야합니다.


Constraint Enable, Disable하기예제(state에서 4가지가 있다는 걸 기억하세요)

테이블에 어떤 Constraints가 있었는지 확인하기

SQL> select TABLE_NAME, CONSTRAINT_NAME, STATUS, DEFERRABLE, INDEX_NAME
  2  from dba_constraints
  3  where TABLE_NAME='TEST_KOO';

TABLE_NAME                     CONSTRAINT_NAME                STATUS   DEFERRABLE     INDEX_NAME
------------------------------ ------------------------------ -------- -------------- ------------------------------
TEST_KOO                       TEST_KOO_NAME_NN               ENABLED  NOT DEFERRABLE
TEST_KOO                       TEST_KOO_ID_PK                 ENABLED  DEFERRABLE     TEST_KOO_ID_PK
 

Enabling Constraints
ALTER TABLE scott.test_koo
ENABLE NOVALIDATE CONSTRAINT test_koo_id_pk;
(or ENABLE VALIDATE CONSTRAINT test_koo_id_pk
or ENABLE CONSTRAINT test_koo_id_pk)
 

*여기서 ENABLE CONSTRAINT만 적으면 디폴트값인 ENABLE VALIDATE CONSTRAINT가 됩니다.

Disabling Constraints
alter table scott.test_koo
disable novalidate constraint test_koo_id_pk;
(or disable validate constraint test_koo_id_pk;
or disable constraint test_koo_id_pk;)
 

*여기서 DISABLE CONSTRAINT만 적으면 디폴트값인 DISABLE NOVALIDATE CONSTRAINT가 됩니다.

** Constraint Enable시 주의사항
1. ENABLE VALIDATE CONSTRAINT의 경우 앞에서 설명했다시피, 기존의 자료들도 모두 검증을 해야 새로운 자료를 insert할 수 있습니다.
여기서 기존의 자료들을 모두 검증을 하기 전까지는 어떠한 사용자도 해당 테이블을 사용하지 못하게 LOCK를 걸어버리기 때문에 업무시간에는 사용에 주의하십시오.
2. 만약에 constraint해제했다가 다시 set하는 경우에 제약조건에 어긋나는 값이 컬럼에 입력되었을 수도 있습니다.
이런 invalid data가 있으면 query가 슬그머니 죽어버립니다. 어디서 어떤 값이 문제가 되는지도 모르고 말이죠.
---------->  문제가 되는 값(invalid data)를 찾기 위해 사용하는 테이블이 EXCEPTION 테이블입니다.



EXCEPTION 테이블 사용하기
* DBCA로 DB생성하셨으면 exception table이 기본적으로 존재하지만 CREATE DATABASE로 수동DB생성하셨으면
utlexcptl.sql을 실행하셔서 생성해주세요
.

TABLE_NAME                     CONSTRAINT_NAME                STATUS   DEFERRABLE     INDEX_NAME
------------------------------ ------------------------------ -------- -------------- ------------------------------
TEST_KOO                       TEST_KOO_NAME_NN               ENABLED  NOT DEFERRABLE
TEST_KOO                       TEST_KOO_ID_PK                 ENABLED  DEFERRABLE     TEST_KOO_ID_PK

SQL> alter table scott.test_koo
  2  disable constraint test_koo_id_pk;

Table altered.

SQL> insert into scott.test_koo
  2  values(1,'Koo');

1 row created.

SQL> insert into scott.test_koo
  2  values(1,'Young');

1 row created.

SQL> alter table scott.test_koo
  2  enable constraint test_koo_id_pk
  3  exceptions into exceptions;
alter table scott.test_koo
*
ERROR at line 1:
ORA-02437: cannot validate (SCOTT.TEST_KOO_ID_PK) - primary key violated

SQL> select rowid, id from scott.test_koo
   2  where rowid in(select row_id from exceptions);

하게 되면 오류가 난 행의 ROWID를 출력합니다.
그 후 ROWID를 참조해서 update를 하면 됩니다.

SQL> UPDATE scott.test_koo
  2  set id=3
  3  where rowid='AAANExAAEAAAAVMAAB';

오류 수정후 
alter table scott.test_koo
enable constraint test_koo_id_pk;