[풀스택과정] 데이터베이스 11. 데이터 무결성

* 이 글은 제가 공부하기 위해 최대한 사실에 입각해 내용을 적으려고 하지만 일부 내용들이 정확하지 않을 수 있습니다.

   혹시나 잘못된 부분이 있으면 너그럽게 이해해 주시고 피드백 부탁드려요!

---

 

1. 데이터 무결성 제약조건의 개념

데이터 무결성 제약조건의 개념

• 데이터 무결성 제약조건의 개념

  – 데이터의 정확성과 일관성을 보장
  – 데이터의 정확성을 유지하여 다양한 종류의 업무규칙 고려한 예

    • student 테이블에서 학년 데이터는 1,2,3,4 중의 하나의 값만 입력
    • 모든 학번은 유일
    • student 테이블의 지도교수 번호는 professor 테이블의 교수번호 중의 하나와 반드시 일치

• 데이터 무결성 제약조건의 장점

  – 테이블 생성 시 무결성 제약조건을 정의 가능
  – 테이블에 대해 정의, 데이터 딕셔너리에 저장되므로 응용 프로그램에서 입력된 모든 데이터에 대해 동일하게 적용
  – 제약조건을 활성화, 비활성화할 수 있는 융통성

 

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

[표 12.1] 무결성 제약조건 종류

 

 

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

NOT NULL 무결성 제약조건

• 해당 칼럼의 값이 NULL을 가질 수 없다는 것을 정의하는 제약조건
• 테이블레벨이 아닌 열 레벨로만 지정가능하다.
• NOT NULL 무결성 제약조건이 지정된 칼럼은 반드시 값을 입력해야 함. 생략 시 오류 발생
• 기존 데이터를 NULL로 수정하는 경우에도 오류 발생

고유 키 (unique key) 무결성 제약조건

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

기본 키(primary key) 무결성 제약조건

• 기본 키는 테이블 내에 저장된 행을 유일하게 식별할 수 있는 식별자로 사용
• 관계형 데이터 모델에서 테이블은 행을 원소로 가지는 집합으로 정의되어 집합 내의 모든 행을 구별할 수 있는

식별자가 필요

• 기본 키 무결성 제약조건은 하나 이상의 칼럼에 의해 테이블의 모든 행을 구별하기 위한 식별자를 정의하기 위한 

제약 조건
• 고유 키 제약조건과 NOT NULL 제약조건을 결합한 개념

 

참조 무결성 제약조건

• 한 테이블의 칼럼 값이 자식, 다른 테이블의 칼럼 값 중에 하나와 일치시키기 위한 제약조건
• 용어

  - 자식 테이블 : 다른 테이블의 칼럼 값을 참조하는 테이블
  - 부모 테이블 : 다른 테이블에 의해 참조되는 테이블
  - 외래 키 : 부모 테이블의 칼럼 값을 참조하는 자식 테이블의 칼럼
  - 참조 키 : 자식 테이블에서 참조하는 부모 테이블의 칼럼

 

[표 12.2] 갱신 제한과 삭제 제한에서 허용되는 DML 명령문

CHECK 무결성 제약조건

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

  – student 테이블에서 학년 데이터는 1,2,3,4 중의 하나의 값만 입력 가능

 

 

 

3. 무결성 제약조건의 생성 방법

무결성 제약조건 생성 방법

• 무결성 제약조건 생성

– 테이블 생성과 동시에 정의
– 테이블을 생성한 이후에 추가, 삭제할 수 있음
– 제약조건명을 지정하지 않으면 SYS_Cn 형태로 자동생성
– 칼럼 레벨 : 칼럼 정의 시 해당 칼럼별로 지정
– 테이블 레벨 : 제약조건이 하나 이상의 칼럼을 참조, 지정

☞ 사용법

칼럼 레벨에서 제약조건 정의

• column_constraint : 칼럼 레벨에서 제약 조건을 정의하는 경우

테이블 레벨에서 제약조건 정의

• table_constraint : 테이블 레벨에서 제약 조건을 정의하는 경우

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

• ON DELETE CASCADE 

  – 부모 테이블에서 외래 키가 참조하는 기본 키나 고유 키를 포함한 행을 삭제한 경우 자식 테이블의 외래 키를 포함하는

    행도 함께 삭제

• USING INDEX

  – 기본 키나 고유 키 무결성 제약조건 생성 시 묵시적으로 생성되는 인덱스에 대한 스토리지 파라미터 정의

• NOT DEFERRABLE : 

  – 하나의 DML 명령문이 처리될 때마다 제약조건 위반 여부를 검사, defer_spec을 명시하지 않을 경우에는 기본 값

• DEFERRABLE

  – 트랜잭션 내의 모든 DML 명령문에 대한 제약조건 검사를 트랜잭션 종료 시까지 연기

• INITIALLY IMMEDIATE 

  – DML 명령문이 종료될 때마다 제약조건 검사

• INITIALLY DEFERRED 

  – 트랜잭션이 끝날 때만 제약조건 검사

 

무결성 제약조건 생성 예

강좌(subject) 테이블 인스턴스

수강(sugang) 테이블 인스턴스

무결성 제약조건 조회

• USER_CONSTRAINTS 데이터 사전에서 무결성 제약조건 조회

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

• NULL을 제외한 무결성 제약조건 추가

  – ALTER … ADD CONSTRAINT 명령문 사용

☞ 사용법

• NULL 무결성 제약 조건 추가

  – ALTER TABLE…MODIFY 명령문 사용

☞ 사용법

무결성 제약조건 추가 예

☞ 사용 예

학생 테이블 인스턴스를 참조하여 studno에 기본 키, idnum에 고유 키, name에 NOT NULL 무결성 제약조건을 

추가하여라.

• 실습 예 1

– p44의 부서 테이블 인스턴스를 참조하여 deptno에 기본 키, dname에 NOT NULL 무결성 제약조건을 추가하여라.

add constraints primary key dept_pk(deptno); 
alter table department modify ( dname not null);

• 실습 예 2

– p44의 교수 테이블 인스턴스를 참조하여 profno에 기본 키, name에 NOT NULL, deptno에 참조 무결성 제약조건을 추가하여라.

alter table professor add constraints prof_pk primary key(profno);
alter table professor modify (name not null);
alter table professor add constraints prof_fk 
foreign key(deptno) references department(deptno);

 

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

• 즉시 제약조건(immediate constraints)에 위배되는 데이터 입력 시

  – 테이블에 데이터를 먼저 입력한 다음 무결성 제약조건을 위반하는 명령문을 롤백

• 지연 제약조건(deferred constraints)에 위배되는 데이터 입력 시

  – 트랜잭션 내의 DML 명령문에서 제약조건 검사를 COMMIT 시점에서 한꺼번에 처리하여 트랜잭션의 처리 성능을

    향상하기 위해 사용

 

즉시 제약조건 위배되는 데이터 입력

☞ 사용 예

강좌 테이블에서 무결성 제약조건에 위반되는 데이터의 예를 입력하여라.

지연 제약조건 위배되는 데이터 입력

☞ 사용 예

강좌 테이블의 무결성 제약조건(subject_id_pk)에 위반되는 데이터를 입력하여 오류 메시지를 확인하여라.

☞ 사용 예

무결성 제약조건 삭제

• 무결성 제약조건 삭제 시

  – ALTER TABLE … DROP CONSTRAINT 명령문 사용
  – 부모 테이블의 기본키 무결성 제약조건을 삭제하는 경우

    • 참조 무결성 제약조건을 먼저 삭제한 후 삭제하거나 CASCADE 옵션 사용

☞ 사용법

– cascade : 삭제되는 칼럼을 참조하는 참조 무결성 제약조건도 함께 삭제

 

☞ 사용 예

강좌 테이블의 subject_pk_ck 무결성 제약조건을 삭제하여라

 

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

• 대용량 데이터 초기 입력 시

  – 무결성 제약조건의 위반 여부를 검사하는 과정으로 인해 처리시간이 오래 걸리는 경우가 발생
  – 이러한 경우에는 기존의 무결성 제약조건을 일시적으로 비활성화하여 데이터를 입력한 다음, 비활성화된 무결성

    제약조건은 다시 활성화
  – ALTER TABLE 명령문에서 ENABLE 또는 DISABLE 절 사용

 

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

• 제약조건의 비활성화

☞ 비활성화 사용 예

– 수강 테이블의 sugang_pk, sugang_studno_fk 무결성 제약조건을 비활성화하여라.

무결성 제약조건의 활성화

• 제약조건의 활성화

  – NOVALIDATE : 기존 데이터에 대해서는 제약조건을 적용하지 않고, 새로 입력되는 데이터나 수정하는 데이터에

    대해서만 제약조건을 검사하기 위한 옵션

 

• 활성화 사용법

  – 수강 테이블의 sugang_pk, dugang_studno_fk 무결성 제약조건을 활성화하여라.

무결성 제약조건 조회 1

• USER_CONSTRAINTS 

  – 제약조건이 설정된 테이블 이름, 무결성 제약조건 이름, 무결성 제약조건 종류 및 활성화 상태정보를 저장

☞ 사용 예

student, professor, department 테이블에 정의된 모든 종류의 무결성 제약조건을 조회하여라.

• USER_CONS_COLUMNS

  – 제약조건이 설정된 칼럼 이름을 저장