[Admin] 데이터 무결성 제약조건

◆ 데이터 무결성 제약조건이란?
데이터의 정확성과 일관성을 보장하기 위해 테이블 생성시에 각 컬럼에 대해 정의하는 규칙을 의미.


◆ 데이터 무결성 제약조건의 종류

무결성 제약 조건	설명
NOT NULL	해당 컬럼값은 널을 포함할 수 없음.
UNIQUE	테이블에서 해당 컬럼값은 반드시 유일해야 함.
PRIMARY KEY	해당 컬럼값은 반드시 존재해야 함며, 유일해야 함.  NOT NULL + UNIQUE 제약조건이 결합된 형태.
REFERENCE	해당 컬럼값은 참조되는 테이블의 컬럼 값 중의 하나와 일치하거나 널을 가짐.
CHECK	해당 컬럼에 저장 가능한 데이터 값의 범위나 조건 지정.

◆ 데이터 무결성 제약조건의 특징
  - 테이블 생성 시 무결성 제약조건을 정의하여 프로그래밍 과정을 줄여준다.
  - 데이터베이스 서버에 의해 무결성 제약조건이 관리되어 데이터 오류 발생 가능성을 줄여준다.
  - 테이블에 대해 정의되고 데이터 딕셔너리에 저장된다.
  - 일시적으로 활성화 또는 비활성화 할 수 있다.


◆ 기본키 무결성 제약 조건
기본키란 테이블 내에 저장된 행을 유일하게 식별할 수 있는 식별자를 의미.
하나 이상의 컬럼에 대해 테이블의 모든 행을 구별하기 위한 식별자를 정의.(Unique key + Not null 개념)
테이블의 컬럼에 Primary key나 Unique key가 걸리면 해당 컬럼에는 자동적으로 Unique Index가 만들어진다.
이 상태에서 해당 컬럼의 제약조건을 disable시키면 Unique Index가 지워진다.
이후 다시 해당 컬럼의 제약조건을 enable시키면 Unique Index를 다시 재생성하는데 이때 시간이 오래 걸린다.


◆ 참조 무결성 제약조건
한 테이블의 컬럼값이 자식 또는 다른 테이블의 컬럼값 중 하나와 일치시키기 위한 제약조건.
  - 자식 테이블(Child Table) : 다른 테이블의 값을 참조하는 테이블
  - 부모 테이블(Parent Table) : 다른 테이블에 의해 참조되는 테이블
  - 외래키(Foreign Key) : 부모 테이블의 값을 참조하는 자식 테이블의 컬럼
  - 참조키(Reference Key) : 자식 테이블에서 참조하는 부모 테이블의 컬럼

참조 무결성 제약조건을 걸어줄 때에는 외래키와 참조키 컬럼에 인덱스가 생성되어 있어야 한다.(성능 좋아짐)
사원 테이블에 데이터가 1억건, 부서 테이블에 100만건이 있다고 가정하자.
사원 테이블에 부서번호 5000을 가진 데이터가 들어올 때 부서 테이블에서 부서번호 5000을 가진 데이터를 비교하는데 운이 조하서 첫 줄에 부서번호 5000을 찾았다고 했을 때 과연 거기서 스캔을 멈출까? 결과는 그렇지 않다. 5000번의 부서번호가 또 있는 줄 알고 끝까지 스캔을 한다.

---

◆ 무결성 제약조건 생성 방법
무결성 제약조건의 생성 방법에는 테이블을 생성할 때 제약조건을 주는 방식이 있고, 테이블을 생성 후 나중에 추가하는 방법이 있다.

◇ 테이블 생성시 무결성 제약조건 생성

SQL> create table 테이블명
   (컬럼명 데이터타입 [default 기본값]
      [constraint 제약조건명 제약조건타입]
      [, ...]);

정식 : no number(5) constraint subject_no_pk primary key
약식 : no number(5) primary key

정식과 약식의 차이점은 제약조건을 일시적으로 활성화 또는 비활성화 할 때 제약조건명이 필요한데 그때는 정식에서 사용한 제약조건명을 사용하면 편하다.
하지만 약식으로 하면 오라클이 자기 마음대로 제약조건명을 정하기 때문에 필요시 조회를 해야 하는 불편함이 있다. 그러나 제약조건을 걸 때는 간편하다.

name varchar2(20)
   constraint subject_name_nn not null
   constraint subject_name_uk unique,

테이블의 한 컬럼에 여러 개의 제약조건을 줄 때는 콤마 없이 기술해주면 된다.


◇ 기존 테이블에 무결성 제약 조건 추가

SQL> alter table 테이블명
   add [constraint 제약조건명] 제약조건타입 (제약조건걸어줄컬럼명);

단, Not Null 제약조건 추가는 Not Null 허용 상태를 Not Null 불가로 변경하는 것이므로 modify 명령문을 사용해야 한다.

SQL> alter table 테이블명
   modify (제약조건걸어줄컬럼명 [constraint 제약조건명] NOT NULL);

◆ 테이블 생성 후 참조 무결성 제약조건 추가

SQL> alter table 테이블명
   add constraint 제약조건명 foreign key (제약조건걸어줄컬럼명) references 참조테이블명 (제약조건걸어줄컬럼명);

◆ 무결성 제약조건 삭제

SQL> alter table 테이블명
   drop constraint 제약조건명 [cascade];

◆ 무결성 제약조건 비활성화

SQL> alter table 테이블명
   disable constraint 제약조건명 [cascade];

◆ 무결성 제약조건 활성화

SQL> alter table 테이블명
   enable [novalidate] constraint 제약조건명 [cascade];

novalidate : 기존 데이터는 적용시키지 않고 새로 입력되거나 수정되는 데이터에만 적용.

---

◆ 제약조건 관련 4가지 상태
  ◇ disable novalidate
    - 기본값
    - 그 제약조건을 완전히 없는 셈 친다. 없는 것과 동일함. 
    - 우리가 Primary key나 Unique key를 걸면 자동으로 Unique Index가 만들어지는데 disable해버리면 그 인덱스가 자동으로 지워진다. 후에 다시 enable하게 되면 자동으로 다시 만들게 되서 시간이 오래 걸림.

  ◇ disable validate
    - 데이터를 더이상 변경 안되게 막는 것.
    - 국민은행 테이블스페이스에 100개의 테이블이 있는데 그 중 고객 테이블만 읽기전용으로 하고 싶으면 disable validate 하면 된다.

  ◇ enable novalidate
    - 기존에 들어있는 데이터는 검사 안하고, 새로 들어온 데이터부터 검사함.

  ◇ enable validate
    - 기본값
    - 기존에 들어있는 데이터를 검사하고, 새로 들어오는 데이터도 검사함.

---

◆ Exceptions Table

위와 같은 상황에서, 구서버의 1억건을 신규서버로 들이붓고 신규서버의 A컬럼의 제약조건을 enable novalidate 시키면 기존에 들어있던 1억1천건은 제약조건 검사를 하지 않고, 이후에 들어오는 데이터들부터 검사한다. 이 방법의 문제점은 기존에 들어있던 데이터들이 제약조건에 위배되는 값이 있었다고 해도 에러가 나지 않는다는 것이다.
그래서 enable validate를 해준다면, 기존에 들어있던 1억1천건부터 제약조건 검사를 하는데, 중간에 위배값이 있다면 에러가 난다. 그러면 그 위배값을 찾아서 제대로 고쳐주고 다시 enable시켜야 한다.
그러나 사용자가 어떻게 무슨 값이 위배되었는지 그 많은 데이터들 중 찾을 수 있는 방법이 있을까?
그때는 Exceptions table을 만들어놓고, 위배값이 나오면 Exceptions table에 저장되도록 하여 후에 위배값을 수정하기 용이하도록 해주면 된다.

conn / as sysdba;
@?/rdbms/admin/utlexcpt.sql      (9i는 utlexcptl.sql)
sysdba권한으로 Exceptions table을 생성시키는 스크립트를 수행한다.

create table scott.test1
   (no number(5)
       constraint test1_no_ck check (no in (10, 20, 30)));
테스트를 위해 무난히 check제약조건을 건 테스트용 테이블을 하나 생성한다.

insert into scott.test1 values (10);
정상적으로 들어간다.

insert into scott.test1 values (40);
제약조건에 위배되므로 에러를 띄운다.

alter table scott.test1 disable constraint test1_no_ck;
check제약조건을 disable시킨다.

insert into scott.test1 values (40);
제약조건을 disable시켜놨기 때문에 정상적으로 들어간다.

select * from scott.test1;
조회해보면 10, 40이 들어있는 것을 확인가능.

alter table scott.test1
   enable validate constraint test1_no_ck
   exceptions into sys.exceptions;
제약조건을 enable시키되 위배되는 로우가 있으면 Exceptions table에 집어넣는다.

select rowid, no
   from scott.test1
   where rowid in (select row_id from exceptions)
   for update;
제약조건이 위배된 로우와 로우아이디를 확인함.

update scott.test1
   set no=20
   where rowid='위에서 구한 ROWID값';
제약조건이 위배된 로우아이디값을 가지고 제대로 된 값으로 수정함.

commit;

truncate table exceptions;
또다른 제약조건 위배가 있는지 확인하기 위해 Exceptions table을 절삭함.

alter table scott.test1
   enable validate constraint test1_no_ck
   exceptions into sys.exceptions;
다시 enable validate를 해보면 아무런 위배 없이 제약조건을 enable시켰음을 확인할 수 있다.

---

◆ Deferred와 Immediate
  - Deferred : 커밋을 날리는 시점에서 제약조건을 검사함.
  - Immediate : DML이 매 실행되는 시점에서 제약조건을 검사함.

create table test1
   (no number(5)
      constraint test1_no_ck check (no in (10, 20, 30))
      deferrable initially deferred);
테이블을 하나 생성한다. 제약조건 테스트를 하기 위해 무난히 check 제약조건을 걸어준다.

alter session set constraint=deferred;
커밋 시점에서 제약조건 검사를 수행하도록 함.

insert into test1 values (40);
분명 잘못된 값인데 에러 안남.

insert into test1 values (10);

commit;
이 시점에서 제약조건 검사를 하기 때문에 커밋 안되고 에러 남. 데이터 안들어갔음.

alter session set constraint=immediate;
DML문장 실행시마다 제약조건 검사를 수행하도록 함.

insert into test1 values (40);
제약조건 위배로 인해 에러남.

insert into test1 values (10);
잘 들어감.

commit;
10데이터는 들어갔음.

---

◆ 무결성 제약조건 관련 데이터 딕셔너리
사용자에 의해 정의된 무결성 제약조건을 조회하기 위한 데이터 디셔너리 뷰는 USER_CONSTRAINTS, USER_CONS_COLUMNS가 있다.

USER_CONSTRAINTS
  - 제약 조건이 정의된 테이블 이름, 제약조건 이름, 제약조건 종류 및 활성화 상태 정보 저장

USER_CONS_COLUMNS
  - 제약조건이 정의된 컬럼과 제약조건 이름 저장