Integrity Constraints

상위 문서: SQL

개요

Integrity constraint는 승인된 사용자에 의해 데이터베이스에 적용된 갱신(update)에 의한 변경 데이터의 일관성(consistency)를 잃지 않도록 보장해준다. 즉, integrity constraint는 데이터베이스를 우발적인 손상으로부터 보호하는 역할을 한다. Integrity constraint의 예는 다음과 같다:

• 강사(instructor)의 이름은 null일 수 없다.
• 두 명의 강사가 동일한 ID를 가질 수 없다.
• course 릴레이션의 모든 학과(department) 이름은 department 릴레이션에 존재해야 한다.
• 학과의 예산은 반드시 $0.00보다 커야 한다.

일반적으로 데이터베이스에 관련된 임의의 논리 조건(predicate)은 integrity constraint가 될 수 있다. 하지만 어떤 조건은 검사 비용이 높을 수 있기 때문에 대부분의 DBMS는 비용이 적게 드는(minimal overhead) 제약 조건(constraint) 만을 명시하도록 한다.
Integrity constraint는 보통 데이터베이스 스키마(schema) 설계 과정의 일부로 인식되며, create table 명령의 일부로 릴레이션을 생성할 때 선언된다. 또한 아래와 같이 기존 릴레이션에 integrity constraint를 추가할 수 있다.

alter table <table name> add <constraint> ...

Integrity constraint에는 primary key constraint나 foreign key constraint도 존재하지만 해당 문서에는 다른 종류의 제약 조건 대해 알아본다.

Constraints on a Single Relation

create table 명령은 테이블을 정의하는 것 외에도 integrity constraint를 포함할 수 있다. 이때 primary key 외에도 다음과 같은 integrity constraint가 포함될 수 있다:

• not null
• unique
• check(<조건>)

해당 문서에서는 위 constraint에 대해 설명한다.

Not null constraint

null 값은 모든 속성(atrribute)의 도메인에 포함되기 때문에 기본적으로 모든 속성에서 허용되지만 특정 속성에서는 null 값의 사용이 부적절하다. 에를 들어 student 릴레이션에서 name이 null인 튜플은 누군지 모르는 학생에 대한 정보를 담게 되므로 부적절하다다. 마찬가지로 학과의 예산(budget)이 null인 것도 바람직하지 않다. 이런 경우, 해당 속성에 null 값이 사용되는 것을 막기 위해 다음과 같이 create table 명령어를 작성할 수 있다.

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

not null 제약 조건은 해당 속성에 null 값의 삽입을 금지하며, 이는 도메인 제약(domain constraint)의 한 예이다. 만약 not null로 선언된 속성에 null을 삽입하려는 데이터베이스 수정이 시도되면, 오류 메시지가 발생한다. 이처럼 null을 피하고 싶은 상황은 많다. 특히, SQL은 primary key에 null 값 사용을 허용하지 않는다. 예를 들어, department 릴레이션에서 dept_name이 기본 키로 선언되어 있다면, 별도로 not null을 선언하지 않아도 null은 자동으로 금지된다.

Unique Constraints

SQL은 다음과 같은 integrity constraint도 지원한다:

unique (Aj1, Aj2, ..., Ajm)

unique 제약은 나열된 속성들이 superkey를 이룬다는 것을 의미한다. 즉, 이 속성들의 값이 모두 같은 두 튜플은 존재할 수 없다. 이때 not null로 명시하지 않는 이상, null 값이 허용되기 때문에, null 값이 사용된 중복된 두 튜플은 중복 가능하다.

The check cluase

릴레이션 선언에 check(P) 절을 적용하면, 모든 튜플이 조건 P를 만족해야 한다는 뜻이 된다. check 저른 속성 값이 특정 조건을 만족하는지 보장하는데 자주 사용된다. 예를 들어

check (budget > 0)

위와 같은 코드가 create table 명령어에 포함되면, budget 값이 0보다 커야함을 나타낸다. 또한 아래와 같이 사용될 수도 있다:

create table section (
  course_id varchar(8),
  sec_id varchar(8),
  semester varchar(6),
  year varchar(4,0),
  building varchar(15),
  room_number varchar(7),
  time_slot_id varchar(4),
  primary key (course_id, sec_id, semester, year),
  check (semester in ('Fall', 'Winter', 'Spring', 'Summer'))
);

위 코드에서는 check 절을 이용해 semester 속성의 값을 'Fall', 'Winter', 'Spring', 'Summer' 중 하나로 열거형(enum)처럼 제한하고 있다.

check 절에서 null은 특수한 경우이다. check 조건이 false가 아니라면, 즉 unknown(= null로 인한 결과) 이더라도 조건을 만족한 것으로 간주된다. 그래서 null 값을 막고 싶다면, not null 제약을 따로 명시해야 한다.

Referential integrity

참조 릴레이션(referencing relation)의 특정 속성 집합에 존재하는 값이 참조되는 릴레이션(referenced relation)의 특정 속성 집합에 존재하도록 보장하고자 할 때가 있다. 이러한 조건을 referential integrity constraints(참조 무결성 제약 조건)라고 하며, foreign key는 해당 조건의 한 형태이다.
Foreign key로 참조되는 속성 값들은 참조되는 릴레이션의 기본키에 해당하므로, 해당 릴레이션 내에 반드시 존재해야 한다. Foreign key는 SQL DDL의 create table문 안에서 foreign key절을 사용하여 지정할 수 있다. 예를 들어 couree 테이블의 정의는 아래와 같은 참조 무결성(referential integrity)를 포함한다.

foreign key (dept_name) references department

위는 각 course 튜플에 대해 해당 튜플에 명시된 dept_name 속성의 값이 department 릴레이션에도 존재해야 한다는 것을 의미한다. 이 제약 조건이 없다면, 존재하지 않는 학과 이름을 지정한 course가 생길 수 있다.

SQL은 references 절의 확장 형태도 지원하며, referneced relation의 속성 리스트를 명시적으로 지정할 수도 있다. 예를 들어, course 릴레이션의 foreign key 선언은 다음과 같이 명시될 수 있다.

foreign key (dept_name) references department(dept_name)

이때 명시된 속성 리스트는 반드시 참조되는 릴레이션의 superkey로 선언되어 있어야 한다. 이는 primary key 제약조건이나 unique 제약조건을 통해서 이뤄질 수 있다. 이때 foreign key는 호환 가능한 속성 집합을 참조해야 한다. 즉, 속성의 개수가 같아야 하며, 각 속성의 자료형도 호환되어야 한다. 예를 들면, 아래와 같다:

create table department (
    name varchar(20),
    campus varchar(20),
    primary key (name, campus)
);

create table course (
    dept_name varchar(20),
    dept_campus varchar(20),
    foreign key (dept_name, dept_campus) references department(name, campus)
);

위에서 foreign key인 (dept_name, dept_campus)은 department 테이블의 primary key인 (name, campus)를 참조한다. 즉 foreign key가 두 개이므로 참조할 대상도 두 개이다. 이때, dept_name과 name의 자료형이 다르거나, dept_campus와 campus의 자료형이 다를 경우 오류가 생긴다.

Cascading Actions in Referential Integrity

Referential integrity가 위배될 경우, 일반적인 경우에는 해당 동작을 유발한 동작을 취소한다. 하지만 foreign key 절에서는 참조되는 릴레이션에 대한 삭제(delete)나 갱신(update) 작업이 제약 조건을 위배하는 경우, 해당 동작을 거부하지 않고 제약 조건을 복구하기 위한 조치를 취하도록 명시할 수 있다. 다음은 course 릴레이션에 대한 무결성 제약조건 정의 예이다:

create table course (
    ...
    foreign key (dept_name) references department
    on delete cascade
    on update cascade,
    ...
);

위 코드 내에는 on delete cascade절이 존재한다. 해당 절은 department 테이블에서 어떤 튜플을 삭제하는 것이 참조 무결성 제약 조건을 위반하더라도 해당 삭제를 거부하지 않도록 한다. 대신, 해당 삭제는 course 테이블에 연쇄(cascade)되어 해당 department를 참조하는 튜플도 함께 삭제한다. 마찬가지로 on update cascade절은 제약 조건을 위반하는 필드의 갱신을 거부하지 않고, course 테이블 내의 참조하는 튜플들의 dept_name 필드를 새로운 값으로 갱신한다.

Integrity Constraint Violation During a Transaction

SQL은 또한 외래 키 절에서 cascade 이외의 동작도 지정할 수 있도록 한다. 제약조건이 위배될 경우, 참조하는 속성(dept_name)의 값을 null로 설정할 수 있고(set null), 도메인의 기본값으로 설정할 수도 있다. 예를 들어, 아래의 예시를 확인해보자.

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

위 코드는 어떤 사람의 가족 관계를 저장하며, mother와 father 필드는 같은 person table의 다른 사람을 참조하는 foreign key이다. 즉 어떤 사람이 정의되기 위해서는 그 사람의 부모가 먼저 person 테이블에 존재하여야 한다. 하지만, 부모를 먼저 테이블에 삽입하기 전에 자식을 먼저 테이블에 정의하고자 한다면, 새로운 튜플을 삽입할 때 무결성 제약조건을 위반할 수 있다. 이를 해결하는 방법은 세가지가 존재한다.

1. 자식을 삽입하기 전, 부모를 먼저 삽입한다.
2. 부모를 우선 NULL로 삽입 후에 나중에 업데이트한다.
   • 이 방법은 father, mother 속성이 not null로 선언되지 않은 경우에만 가능하다.
3. defer constraint checking
   • SQL에서 제약조건 검사를 transaction 끝으로 연기하는 방식이다,
   • 이 경우 transaction 도중에는 임시로 위반 상태일 수 있지만, 이를 commit할 시점에는 모든 조건이 만족되어야 한다.

Complex Check Conditions and Assertions

Complex Check Conditions

SQL 표준에는 무결성 제약 조건을 명시하기 위한 추가적인 구조들이 존재한다. 예를 들어 check 절 내에 있는 predicate는 subquery를 포함하고 있을 수 있으며, 이 경우, 아래와 같이 참조 무결성 제약 조건을 section 릴레이션에 대해 명시할 수 있다.

check (time_slot_id in (select time_slot_id from time_slot))

해당 check 조건은 section 릴레이션의 각 튜플 내에 있는 time_slot_id가 실제로 time_slot 릴레이션에 존재하는 시간 슬롯의 식별자인지를 검증한다. 따라서 해당 조건은 section 내에 새로운 튜플이 삽입되거나 수정될 때 뿐만 아니라, time-slot 릴레이션이 변경될 때에도 검사되어야 한다.

Assertions

Assertion은 우리가 데이터베이스가 항상 만족하길 바라는 조건을 표현한다. 아래는 assertion을 사용해 표현할 수 있는 제약 조건의 예시이다.

1. student 릴레이션의 각 튜플에 대해, tot_cred 속성의 값은 그 학생이 성공적으로 수강한 과목들의 학점 합과 같아야 한다.
2. 하나의 교수가 한 학기에 같은 time_slot일때, 서로 다른 강의실에서 수업을 할 수는 없다.

SQL에서 assertion은 다음과 같은 형식을 가진다.

create assertion <assertion-name> check <predicate>;

그리고 이를 통해서 첫번째 제약조건을 SQL로 표현하면 다음과 같다.

create assertion credits_earned_constraint check
(not exists (select ID
             from student
             where tot_cred <> (select coalesce(sum(credits), 0) 
                                from takes natural join course
                                where student.ID= takes.ID
                                      and grade is not null and grade<> ’F’ )))

위 코드는 "student 릴레이션의 각 튜플에 대해, tot_cred 속성의 값은 그 학생이 성공적으로 수강한 과목들의 학점 합과 같아야 한다."라는 assertion을 구현한 예시를 보여준다.