데이터 무결성(By Oracle)

□ 데이터 무결성 제약조건의 개념
  ○ 데이터 무결성 제약조건의 개념
    - 데이터의 정확성과 일관성을 보장
    - 데이터의 정확성을 유지하여 다양한 종류의 업무규칙 고려한 예
      → student 테이블에서 학년 데이터는 1,2,3,4 중의 하나의 값만 입력
      → 모든 학번은 유일
      → student 테이블의 지도교수 번호는 professor 테이블의 교수 번호중의 하나와 반드시 일치
□ 데이터 무결성 제약조건의 장점
  ○ 테이블 생성시 무결성 제약조건을 정의 가능
  ○ 테이블에 대해 정의, 데이터 딕셔너리에 저장되므로 응용 프로그램에서 입력된 모든 데이터에 대해 동일하게 적용
  ○ 제약조건을 활성화, 비활성화 할 수 있는 융통성

□ 고유키 무결성 제약조건

   ○ 한 테이블 내에서 칼럼이 동일한 값을 가질 수 없음을 정의하는 제약조건
  ○ 열레벨 또는 테이블 레벨로 정의할 수 있습니다.
  ○ NULL 은 고유 키 제약조건에 위배되지 않으므로 입력할수 있다.(NULL은 항상 UNIQUE함)
  ○ 고유 키 무결성 제약조건이 정의 되면 테이블에 제약조건과 같은 이름의 인덱스가 자동적으로 생성

 

□ 기본 키 무결성 제약조건

  ○ 기본 키는 테이블내에 저장된 행을 유일하게 식별할 수 있는 식별자로 사용
  ○ 관계형 데이터 모델에서 테이블은 행을 원소로 가지는 집합으로 정의되어 집합내의 모든 행을 구별할 수 있는 식별자가 필요
  ○ 기본 키 무결성 제약조건은 하나 이상의 칼럼에 의해 테이블의 모든 행을 구별하기 위한 식별자를 정의하기 위한 제약 조건
  ○ 고유 키 제약조건과 NOT NULL 제약조건을 결합한 개념

 

□ 참조 무결성 제약조건

  ○ 한 테이블의 칼럼 값이 자신, 다른 테이블의 칼럼 값 중에 하나와 일치시키기 위한 제약조건
  ○ 용어
    - 자식 테이블 : 다른 테이블의 칼럼 값을 참조하는 테이블
    - 부모 테이블 : 다른 테이블에 의해 참조되는 테이블
    - 외래 키 : 부모 테이블의 칼럼 값을 참조하는 자식 테이블의 칼럼
    - 참조 키 : 자식 테이블에서 참조하는 부모 테이블의 칼럼

 

□ CHECK 무결성 제약조건

  ○ 칼럼에서 허용 가능한 데이터의 범위나 조건을 지정
  ○ 데이터 입력이나 수정 시 실수로 부정확한 값을 입력 예방
  ○ 하나의 컬럼에 여러 개의 CHECK 무결성 제약조건을 지정 가능
  ○ CURRVAL, NEXTVAL과 같은 가상 칼럼이나 SYSDATE, USER와 같은 함수는 사용 불가능

□ 무결성 제약조건 생성방법

  ○ 테이블 생성과 동시에 정의
  ○ 테이블을 생성한 이후에 추가, 삭제 할수 있음
  ○ 제약조건명을 지정하지 않으면 SYS_Cn 형태로 자동생성
  ○ 칼럼 레벨 : 칼럼 정의시 해당 칼럼별로 지정
  ○ 테이블 레벨 : 제약조건이 하나 이상의 칼럼을 참조, 지정
  ○ CREATE TABLE [schema.] table
     (column datatype[DEFAULT expression]
     [column_constraint],
     [table_constraint][,…]);

--column_constraint : 칼럼 레벨에서 제약 조건을 정의하는 경우
column_constraint :==
{[NOT] NULL
|PRIMARY KEY[USING INDEX index_clause]
|UNIQUE[USING INDEX index_clause]
|REFERENCES[schema.]table[(column[.column]…)]
[ON DELETE CASCADE]
|CHECK (condition)}
defer_spec :==
[NOT DEFERRABLE
|DEFERRABLE[INITIALLY{IMMEDIATE | DEFERRED}]]
[DISABLE | ENABLE[VALIDATE | NOVALIDATE]]

--table_constraint : 테이블 레벨에서 제약 조건을 정의하는 경우
table_constraint :==
{{UNIQUE | PRIMARY KEY} (column[,column]…)
|PRIMARY KEY[USING INDEX index_clause]
|UNIQUE[USING INDEX index_clause]
|FOREIGN KEY (column[.column]…)
REFERENCES[schema.]table[(column[.column]…)]
[ON DELETE CASCADE]
|CHECK (condition)}
defer_spec :==
[NOT DEFERRABLE
|DEFERRABLE[INITIALLY{IMMEDIATE | DEFERRED}]]
[DISABLE | ENABLE]

 

□ 무결성 제약조건 생성문에서의 키워드

  ○ ON DELETE CASCADE
    - 부모 테이블에서 외래 키가 참조하는 기본 키나 고유 키를 포함한 행을 삭제할 경우 자식 테이블의 외래 키를 포함하는 행도 함께 삭제
  ○ USING INDEX
    - 기본 키나 고유 키 무결성 제약조건 생성시 묵시적으로 생성되는 인덱스에 대한 스토리지 파라미터 정의
  ○ NOT DEFERRABLE
    - 하나의 DML 명령문이 처리될 때마다 제약조건 위반 여부를 검사, defer_spec을 명시하지 않을 경우에는 기본 값
  ○ DEFERRABLE
    - 트랜잭션내의 모든 DML 명령문에 대한 제약조건 검사를 트랜잭션 종료 시까지 연기
  ○ INITIALLY IMMEDIATE
    - DML 명령문이 종료될 때마다 제약조건 검사
  ○ INITIALLY DEFERRED
    - 트랜잭션이 끝날 때만 제약조건 검사

 

□ 무결성 제약조건 조회

--USER_CONSTRAINTS 데이터 사전에서 무결성 제약조건 조회
SELECT CONSTRAINT_NAME, CONSTRAINT_TYPE
FROM USER_CONSTRAINTS
WHERE TABLE_NAME IN ('SUBJECT', 'SUGANG')

 

□ 무결성 제약조건에 의한 DML 명령문 영향

  ○ 즉시 제약조건(immediate constraints)에 위배되는 데이터 입력시
    - 테이블에 데이터를 먼저 입력한 다음 무결성 제약조건을 위반하는 명령문을 롤백
  ○ 지연 제약조건(deferred constraints)에 위배되는 데이터 입력시
    - 트랜잭션내의 DML 명령문에서 제약조건 검사를 COMMIT 시점에서 한꺼번에 처리하여 트랜잭션의 처리 성능을 향상시키기 위해 사용

 

□ 무결성 제약조건 활성화 및 비활성화

  ○ 무결성 제약조건의 위반 여부를 검사하는 과정으로 인해 처리시간이 오래 걸리는 경우가 발생
  ○ 이러한 경우에는 기존의 무결성 제약조건을 일시적으로 비활성화하여 데이터를 입력한 다음, 비활성화된 무결성 제약조건은 다시 활성화
  ○ ALTER TABLE 명령문에서 ENABLE 또는 DISABLE 절 사용

--비활성화
ALTER TABLE SUGANG
DISABLE CONSTRAINT SUGANG_PK;

--활성화
ALTER TABLE SUGANG
ENABLE CONSTRAINT SUGANG_PK

 

□ 무결성 제약조건 조회

  ○ USER_CONSTRAINTS : 제약조건이 설정된 테이블 이름, 무결성 제약조건 이름, 무결성 제약조건 종류 및 활성화 상태정보를 저장
  ○ USER_CONS_COLUMNS : 제약조건이 설정된 칼럼 이름을 저장

--USER_CONSTRAINTS
SELECT TABLE_NAME, CONSTRAINT_NAME, CONSTRAINT_TYPE, STAUS
FROM USER_CONSTRAINTS
WHERE TABLE_NAME IN ('SYSDATE', 'PROFESSOR', 'DEPARTMENT');

--USER_CONS_COLUMNS
SLECT TABLE_NAME, COLUMN_NAME, CONSTRAINT_NAME
FROM USER_CONS_COLUMNS
WHERE TABLE_NAME IN ('STUDENT', 'PROFESSOR', 'DEPARTMENT');