Chapter 9. 데이터베이스 주요 기능 (2): Integrity Constraints

MoonLight·2021년 11월 26일

데이터베이스

목록 보기

10/12

I. 무결성 제약조건(IC)

데이터베이스 시스템이 관리하고자 하는 데이터베이스는 단순히 데이터의 모임이 아니고, 특정 조건을 만족하는 데이터베이스이어야 한다. 특정 조건을 만족하는 데이터베이스이어야 사용자에게 의미가 있다. 예를 들어 사람의 나이 속성 값이 음수이면 그 데이터는 정확하지 않은 데이터이며(not accuracy), 사람의 나이가 한 테이블에서는 20이고 동일 사람 나이가 다른 테이블에서는 25이면 데이터가 서로 일치하지(consistency) 않아 잘못된 데이터이다.

무결성 제약은 데이터베이스 시스템이 항상 만족하여야 하는 조건이다.

Ph.D 학생은 조력자를 가져야한다.
은행 계좌의 최소 잔고는 $1000이상이어야 하고
시간당 근로소득은 $6 이상이어야 한다는 등
이와 같이 무결성 제약 예제는 무수히 많다.

무결성 제약은 데이터베이스의 consistency 및 accuracy를 유지하기 위하여 사용된다.

Integrity Constraints, IC는 DB에 승인된 변경이 데이터 consistency가 깨짐으로 인한 안좋은 결과를 내지 않게 함으로써, 데이터베이스에 갑작스러운 상황을 방지한다.
즉, 실세계를 정확히 반영해야 한다는 뜻이기도 하다.

II. 단일 테이블에서 사용가능한 제약조건

not null
- 예., name varchar(20) not null
- 개별 속성에 적용 가능
primary key ( $A_1, A_2, …, A_m$ )
- 반드시 not null이어야 하나, 어자피 자동빵으로 not null이 됨
unique ( $A_1, A_2, …, A_m$ )
- 후보키를 의미 (중복이 있으면 안돼!)
- 얘는 null을 가질 수 있음
check (P)
- 단, P는 predicate (술어부)
- 아래는 semester이 반드시 4개의 값만 가져야 한다는 예제
- 주어진 4개 외의 값으로 변경을 하려는 데이터 변경 및 입력 연산은 실행되지 않는다.

Create table teaches (
    pID 		char(5),
    cID 		char(5),
    semester 		varchar(10),
    year 		numeric(4,0),
    classroom		char(5),
    primary key (pID, cID, semester, year),
    check (semester in (’Spring’, ‘Summer’, ’Fall’, ’Winter’) )
);

III. 참조 무결성 제약조건

정의: 외래 키에 나오는 모든 값은 외래 키가 참조하는 테이블의 주키 값으로 나와야 하는 것

다만 외래 키는 널 값을 가질 수 있으나, 외래 키가 참조하는 주키는 해당 테이블의 주키이므로 널 값이 나올 수 없다.

참조 무결성 제약은 서로다른 두 관계 사이에 존재하며, 동일 관계 내에서도 존재할 수 있다. 참조 무결성 제약은 연관되는 데이터(터플) 연결을 값(value)를 이용하여 하기 때문에 발생하는 현상이다. 만약 터플간의 관계를 값이 아닌 포인터로 연결을 하는 경우에는 참조 무결성이 존재하지 않는다.

예제 1)

참조 무결성을 구체적인 행동(action) 명시 없이 선언할 수 있으며,
이 경우 참조 무결성이 위반되는 경우는 허용하지 않는다.

Create table teaches (
    pID	varchar(5),
    cID	varchar(5),
    …
    primary key(pID, cID, semester, year),
    foreign key(pID) references professor,
    foreign key(cID) references course
);

예제 2)

참조 무결성을 선언함에 있어 참조 무결성이 위반되는 경우가 존재한다면,
이를 해결하는 구체적인 행동까지 명시할 수 있다. 사용자가 명시할 수 있는 행동은 cascade, set null, set default 이다.

Create table teaches (
    pID	varchar(5),
    cID	varchar(5),
    …
    foreign key(pID) references professor,
    on delete cascade,
    on update cascade,
    … 
);

참조 무결성은 외래키를 가지는 테이블에서 선언됨을 알 수 있고, 참조되는 테이블에서는 선언 하는 부분이 없다.

또한 외래키를 선언하면서 함께 명시하는 행동 부분에서 언급하는 delete/update 연산은 참조되는 테이블에 대한 삭제 및 갱신 연산을 의미한다.

on delete cascade, on delete set null, on delete set default와 같이 쓸 수 있다.

`I. on delete 연산`

상기 예제에서 teacher의 pID는 professor 주 키 pID를 참조하는 외래 키이다.

실세계에서 교수가 잘렸으면 당연히 teaches에서도 그 교수관련된 것들은 없애야할 것이다.

professor 테이블의 <100, Kim, ...> 터플이 삭제되면, teaches 테이블에서 참조하는 터플로 인하여 참조 무결성이 위배된다.

이 경우, 행동이 명시되지 않았으면 (on delete 다음에 아무것도 없으면) 아예 삭제 연산이 허용되지 않으며,

cascade인 경우에는 삭제 연산이 teaches 테이블에 파급되어 삭제된 터플을 참조하는 두 터플이 모두 삭제된다.

( teaches의 <100, CS101,...>과 <100, CS201, ...>이 삭제 )

set null 은 해당 테이블 속성 값을 널 값으로 한다. 즉, on delete set null을 적어주었고, pID가 100인 교수가 삭제될 경우 <null, CS101>, <null, CS201> 이 된다.

하지만, 이 예제에는 teaches 테이블 또한 주 키의 일부분에 pID가 있으므로 허용되지 않는다.
teaches의 primary key는 (pID, cID, semester, year) 이었다.

그러나, 참조하는 테이블, teaches의 터플을 삭제하는 것은 참조 무결성 제약을 위배할 가능성이 전혀 없으므로 해당 터플만 삭제된다.

`II. on update 연산`

상기 예제에서 teacher의 pID는 professor 주 키 pID를 참조하는 외래 키이다.

실세계에서 교수번호가 바꼇으면 당연히 teaches에서도 그 교수관련 번호도 바꿔야할 것이다.

professor 테이블의 <100, Kim, ...> 터플이 변경되면, teaches 테이블에서 참조하는 터플로 인하여 참조 무결성이 위배된다.

이 경우, 행동이 명시되지 않았으면 ( on update 다음에 아무것도 없으면 ) 아예 변경 연산이 허용되지 않으며,

cascade인 경우에는 변경 연산이 teaches 테이블에 파급되어 변경된 터플을 참조하는 두 터플이 모두 변경된다.

teaches의 <100, CS101,...>과 <100, CS201, ...>이 변경)

set null 은 해당 테이블 속성 값을 널 값으로 한다. 즉, on update set null을 적어주었고, pID가 100인 교수가 변경될 경우 <null, CS101>, <null, CS201> 이 된다.

하지만, 이 예제에는 teaches 테이블 또한 주 키의 일부분에 pID가 있으므로 허용되지 않는다.
teaches의 primary key는 (pID, cID, semester, year) 이었다.

on delete와 다르게 on update의 경우에서 참조하는 테이블, teaches의 터플을 주키에 있지 않은 놈으로 변경하는 것은 참조 무결성 제약을 위배하므로(참조하는 놈이 참조되는 놈 안에 포함되지 않으므로) 허용되지 않는다.

`IV. 외래키가 있는 상태에서 어떻게 튜플을 삽입할까?`

Create table person (
    ID  		char(10) primary key,
    name 		char(40),
    mother 		char(10),
    father  	char(10),
    foreign key (mother) references person,
    foreign key (father) references person
);

person 테이블이 상기와 같이 정의되었다고 가정하자. 단일 관계에 존재하는 참조 무결성 제약 예제이다.

mother와 father 속성이 외래 키로 선언이 되어 있으므로, person 데이터를 입력하려면 그 사람에 대한 mother, father 정보가 있어야 한다.

이를 해결하는 방법 중에서
첫 번째 방법은

한 자식을 넣으려고 할 때, 그 부모를 person에 먼저 집어넣고, 그 부모를 집어넣고자 할 때 또 그 부모를 집어넣는 방법이다.
- 하지만, 대규모 데이터 입력이 이론적으로는 가능하나 현실적으로는 아주 불편하다

두 번째 방법은

외래키는 NULL이 가능하므로 일단 mother, father을 null로 다 집어 넣고, 나중에 추가적인 갱신 연산을 하는 방법이다.

세 번째 방법은

데이터베이스 시스템은 이러한 상황을 위하여 무결성 제약을 연기하여 점검하는 기능을 제공하는데, deffered라는 것을 사용하는 것이다.
- 이를 사용하면 일단 name, mother, father을 다 집어넣고, 모든 튜플입력이 끝난 뒤에 DBS가 외래키를 검산하는 것이다.

Deferrable Integrity Constraints

연기된 무결성 제약

무결성 제약은 기본적으로는 즉시 실행되나,

무결성 제약을 명시할 때 initially deferred 표현을 하면 무결성 제약 검사 및 행동을 연기할 수도 있다.

또한 트랜잭션 정의 시에 set constraints constraints-list deferred로 무결성 제약 점검을 연기할 수도 있다.

V. 복잡한 무결성 제약

앞에서 살펴본 무결성 제약 외에 사용자가 임의로 정의하는 복잡한 무결성 제약을 지원하는 방법은 check절 조건을 활용하거나 또는 주장(assertion) 기능을 활용하는 것이다.

Create teaches (
    pID char(5) CHECK (pID IN (select pID from professor))
    …
);

SQL표준은 check 조건에 임의의 조건을 허용하므로, 상기 예제와 같이 서브질의를 조건으로 할 수 있다. 상기 예제의 의미는 teaches pID 값은 professor pID 값 중에 하나를 가지는 참조 무결성이다

단, teaches pID가 널 값인 경우는 제외

그러나 이 경우에는 무결성 제약 만족 여부를 teaches 테이블에 변화가 있을 때만으로 한정을 하여 professor 테이블에 변화가 있어도 무결성 제약을 점검하지 않는 문제가 있다.

그리고, 주장(assertion)은 표준 SQL2에 명시되어 있는 사양이며, 사용자 임의의 무결성 제약을 유지하는 방법이다.

일반적으로 상용 데이터베이스 시스템은 check 절에 서브질의를 허용하지 않으며 또한 주장(assertion) 기능을 지원하지 않는다. 그 이유는 check 절 서브질의 및 주장(assertion)을 유지하는 많은 비용이 들어가며 동시에 시스템 성능을 저하시키는 요인이 되기 때문이다.

Assertion 예제

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’)
     )
);

상기 예제는 student 테이블의 totalCredit 속성 값을 점검하는 assertion이다. takes 테이블에서 grade가 널 값이 아니고 ‘F’가 아니면 정상적으로 수업을 수강하고 학점을 받은 과목이므로 이들 과목의 credit 속성 합산은 student 테이블의 totalCredit 속성값과 일치하여야 한다.

참고적으로 SQL 언어는 전체정량자(∀, for all)를 제공하지 않으므로, (∀x) P(x) 표현 대신에 ¬(∃x) ¬(P(x)) 표현을 사용하여 표현하여야 한다.

상기 예제에서 언급되는 테이블(여기서는 student, takes, course)에 대한 변화(데이터 입력, 삭제, 변경)가 있으면 데이터베이스 시스템은 무결성 제약 만족 여부를 매번 점검해야 한다. 즉 데이터베이스 시스템은 세 개 테이블에 대한 변화가 있을 때마다 not exists 조건을 점검해야 하는데, 자원이 많이 소모되는 연산이다.