관계형 데이터베이스: 무결성 제약조건(Integrity Constraint)

데이터베이스 목차

이 글에서는 University Database가 사용된다.

 

무결성 제약조건은 DB에 저장되어있는 데이터의 실세계에서의 조건을 반영하기 위해서 사용한다. DBMS 가 이를 항상 만족시켜준다.

• 박사과정에 있다면 지도교수가 있어야 한다.
• 시급이 최소 6달러는 되어야 한다.

이러한 현실세계의 조건을 반영하기 위해서 사용한다. 주로 사용되는 제약조건 목록은 다음과 같다.

• not null
  : 속성이 null 값을 가지면 안된다.
  

  name varchar(20) not null
  budget numeric(12, 2) not null

• primary key
  : PK를 지정하며, PK에 해당하는 속성들은 null이어서는 안 되고, 중복이 있어서는 안 된다.
  

  primary key(A1, A2, ..., Am)​

  A1, A2,..., Am의 조합을 PK로 사용한다. A1, A2, ... Am은 null 이여서는 안되며, PK는 중복이 있어서는 안 된다.
• unique
  : 후보키를 나타낸다. 
  

  unique(A1, A2, ..., Am)​

  A1, A2, ..., Am의 조합을 후보키로 사용한다. null값을 허용한다는 점에서 PK와는 다르다.
• check(P)
  : Relation의 tuple이 P라는 조건을 반드시 만족시켜야 함을 나타낸다.
  

  check(semester in ('Spring', 'Summer', 'Fall', 'Winter'))​

  semster 속성의 값이 반드시 Spring, Summer, Fall, Winter 중 하나여야 함을 나타낸다.

 

참조 무결성 제약이란, R Relation의 A 속성이 S Relation의 A 속성을 참조할 때, R의 A속성에 있는 모든 값은 S의 A속성 값들의 집합에 포함되어야 한다는 것이다. 이때 null값도 포함된다. 

참조하는 column의 집합을 foreign key(외래 키)라고 하고, foreign key를 이용해서 참조 무결성 제약을 표현한다.

CREATE table teaches (
    pID varchar(5),
    cID varchar(5),
    ...
    primary key(pID, cID, semester, year)
    foreign key(pID) reference professor,
              on delete cascade,
              on update cascade,
    foreign key(cID) reference course
);

pID라는 속성은 professor 테이블에 존재하고, cID라는 속성은 course 테이블에 존재함을 나타낸다.

on delete cascade, onupdate cascade는 추가적인 속성을 나타내는 것으로, cascade가 있는 자리에는 다음 키워드들이 올 수 있다.

• cacade
• set null
• set default

각각의 속성이 어떤 역할을 하는지 알아보자.

 

위에서 표현된 테이블에 delete/update 작업을 수행한다고 하자. 

professor 테이블에서 <100, Kim,.... > 튜플을 삭제한다고 할 때, 

• no action: 다른 테이블(teaches)에서 pID 속성을 참조하고 있기 때문에 튜플을 삭제할 수 없다.
• cascade: pID 100을 참조하는 다른 테이블의 튜플도 연쇄적으로 삭제된다.
• set null: professor 테이블의 튜플이 삭제되고, pID 100을 참조하던 다른테이블의 속성이 null이 된다.

professor 테이블에서 <100, Kim,.... > 튜플을 업데이트한다고 할 때

• no action: 참조 무결성에 영향이 가므로 업데이트되지 않는다.
• cascade: 변경되는 속성을 참조하는 다른 테이블의 속성도 업데이트된다.
• set null: 변경되는 속성을 참조하는 다른 테이블의 속성을 null로 바꾸어도 참조 무결성 제약은 만족되지만, 여기서는 teaches의 PK를 구성하는 column조합에 pID속성이 있으므로 null이 될 수 없다.

참조하고 있는 테이블에서의 업데이트는 참조 무결성 제약을 깰 수 있으므로 허용되지 않는다.

 

check를 사용해서 참조 무결성 제약을 구현할 수도 있다. 하지만 check를 사용한 참조 무결성 제약은 매번 조건을 검사할 때마다 서브 쿼리를 처리해야 하므로 큰 비용이 든다. 

CREATE assertion myVerifyTotalCredit CHECK 
(not exists
    (SELECT s1.sID
     FROM student s1
     WHERE s1.totalCredit <> (SELECT SUM(credit)
                              FROM takes, course
                              WHERE s1.sID = sID
                                and course.cID = takes.cID
                                and grade is not null
                                and grade <> 'F')
	)
);

조건문에 포함되어 있는 테이블에 변경이 일어날 때마다 체크한다. 하지만 비용이 아주 많이 들기 때문에 대부분의 DBMS에서는 허용하지 않는데 이때 Trigger를 사용하면 훨씬 낮은 비용으로 보장할 수 있다.