무결성 제약 조건

무결성(integrity)은 데이터베이스에 든 값이 현실의 실제 값과 맞고 서로 어긋나지 않는 성질입니다. 무결성 제약 조건은 이 성질을 지키려고 DBMS가 틀린 값을 막는 규칙이고, 개체·참조·도메인 무결성 셋이 있습니다. 부모 행을 지울 때 자식 행을 어떻게 할지는 참조 동작으로 정합니다.

학생 표에 학번이 빈 학생이 들어오면 그 학생을 가리킬 방법이 없습니다. 없는 학과 ME에 다니는 학생이나 7학년 학생이 들어와도 데이터는 틀린 채로 남습니다. 사람이 입력할 때마다 살피면 언젠가 놓칩니다. 그래서 규칙을 표에 걸어 두고, 규칙을 어기는 행은 DBMS가 들어오지 못하게 막습니다. 앞 레슨의 키의 종류가 여기서 쓰입니다. 기본키와 외래키마다 지켜야 할 규칙이 있습니다. 외래키가 가리키는 쪽(학과)을 부모, 외래키를 가진 쪽(학생)을 자식이라고 부릅니다. 도메인은 속성이 가질 수 있는 값의 범위입니다. 아래 그림은 학생 표에 들어오려던 행이 규칙에 걸려 막히는 모습과, 부모 행을 지울 때 자식 행이 어떻게 되는지 보여 줍니다.

1개체 무결성

무결성 제약 조건은 틀린 데이터가 들어오지 못하게 DBMS가 지키는 규칙입니다. 들어오려는 네 행은 모두 규칙 하나씩에 걸립니다.

개체 무결성(entity integrity)은 기본키를 이루는 속성이 NULL이거나 겹치면 안 된다는 규칙입니다. 학번이 빈 행은 어느 학생인지 가리킬 수 없고, 이미 있는 101을 또 넣으면 101이 두 명이 됩니다. 기본키가 속성 여럿으로 되어 있으면, 그 가운데 어느 하나도 NULL이면 안 됩니다.

2참조 무결성

참조 무결성(referential integrity)은 외래키 값이 NULL이거나, 부모 릴레이션의 기본키에 있는 값이어야 한다는 규칙입니다. 학과에 ME가 없으니 학과코드가 ME인 행은 막힙니다. 107 강도현처럼 학과코드가 NULL인 것은 됩니다.

3도메인 무결성

도메인 무결성(domain integrity)은 속성 값이 그 속성의 도메인, 곧 정해 둔 형식과 범위 안에 있어야 한다는 규칙입니다. 학생 표는 학년을 1부터 4까지로 정해 두었으므로 7은 막힙니다. 학년 자리에 숫자가 아닌 'A'를 넣어도 형식이 맞지 않아 막힙니다.

4부모 행을 지울 때의 참조 동작

부모 행을 지우려는데 그 행을 가리키는 자식 행이 있으면, 자식 행을 어떻게 할지 정해 두어야 합니다. 이 선택을 참조 동작이라고 합니다. 그림은 RESTRICT·SET NULL·CASCADE 셋을 차례로 보입니다.

RESTRICT(제한)는 자식 행이 있으면 부모 행을 지우지 못하게 막습니다. 학생 104·105·106이 학과 EE를 가리키므로 EE를 지울 수 없습니다. 가리키는 학생이 없는 BA는 지울 수 있습니다.

SET NULL은 부모 행을 지우고, 그 행을 가리키던 자식의 외래키를 NULL로 바꿉니다. EE가 지워지면 학생 104·105·106은 남고 학과코드만 NULL이 됩니다. 학과가 없어져도 학생은 남아야 할 때 고릅니다.

CASCADE(연쇄)는 부모 행을 지울 때 그 행을 가리키던 자식 행도 함께 지웁니다. 학생 102를 지우면 102의 수강 행 둘도 사라집니다. 수강 행은 학생 없이는 뜻이 없으므로 CASCADE가 어울립니다. 지워질 자식 행을 또 가리키는 표가 있으면, 그 표도 CASCADE일 때만 연쇄로 지워지고 아니면 거기서 막힙니다.

예제

블록마다 처음 데이터에서 시작합니다. 오류가 난 문장은 아무것도 바꾸지 않습니다. 행을 넣고 지우는 문법(INSERT·DELETE)과 제약 조건을 다시 거는 ALTER TABLE은 13단원 DDL: CREATE·ALTER·DROP·DML: INSERT·UPDATE·DELETE에서 배웁니다. 여기서는 어떤 행이 막히고 무엇이 남는지만 보면 됩니다.

넣을 때 막히는 행

그림에서 막히던 네 행을 하나씩 넣어 보면 모두 막힙니다. 학번이 빈 행과 이미 있는 학번 101로 넣은 행은 개체 무결성에 걸려, 각각 기본키 열에 NULL을 넣을 수 없다는 오류(ORA-01400)와 값이 겹친다는 오류(ORA-00001)가 납니다. 학과에 없는 ME를 가리키는 행은 가리키는 쪽에 그 값이 없다는 오류(ORA-02291)로 막혀 참조 무결성에 걸립니다. 학년이 7인 행은 학생 표가 학년에 걸어 둔 CHECK 규칙을 어긴다는 오류(ORA-02290)로 막혀 도메인 무결성에 걸립니다.

INSERT INTO 학생
VALUES (NULL, '한별',
    1, 'CS');

ORA-01400: cannot insert
NULL into
("SQLD"."학생"."학번")

INSERT INTO 학생
VALUES (101, '한별',
    1, 'CS');

ORA-00001: unique
constraint
(SQLD.학생_PK) violated
on table SQLD.학생
columns (학번)
ORA-03301: (ORA-00001
details) row with column
values (학번:101)
already exists

INSERT INTO 학생
VALUES (108, '한별',
    1, 'ME');

ORA-02291: integrity
constraint
(SQLD.학생_학과_FK)
violated
- parent key not found

INSERT INTO 학생
VALUES (108, '한별',
    7, 'CS');

ORA-02290: check
constraint
(SQLD.학생_학년_CK)
violated

지울 때: RESTRICT

학생이 가리키는 학과 EE를 지우려 하면, 그 학과를 가리키는 자식 행이 있다는 오류(ORA-02292)로 막힙니다. Oracle은 참조 동작을 적지 않으면 자식이 있는 부모 행을 지우지 못하게 막습니다. RESTRICT와 같은 결과입니다. Oracle은 RESTRICT라는 낱말은 받지 않으므로(ORA-02000) 아무것도 적지 않습니다.

DELETE FROM 학과
WHERE 학과코드 = 'EE';

ORA-02292: integrity
constraint
(SQLD.학생_학과_FK)
violated
- child record found

지울 때: SET NULL

학생 표의 외래키를 ON DELETE SET NULL로 다시 걸고 EE를 지우면, 학생 일곱 명은 그대로 남고 EE를 가리키던 104·105·106의 학과코드만 NULL로 바뀝니다. 107은 처음부터 NULL이었습니다.

ALTER TABLE 학생
  DROP CONSTRAINT
    학생_학과_FK;
ALTER TABLE 학생
  ADD CONSTRAINT
    학생_학과_FK
    FOREIGN KEY (학과코드)
    REFERENCES 학과
      (학과코드)
    ON DELETE SET NULL;
DELETE FROM 학과
WHERE 학과코드 = 'EE';
SELECT 학번,
  이름,
  학과코드
FROM 학생
ORDER BY 학번;

지울 때: CASCADE

이번에는 수강 표의 외래키를 ON DELETE CASCADE로 다시 걸고 학생 102를 지웁니다. 102의 수강 행 둘(DS 70점, OS 60점)이 함께 지워져 수강 표가 12행에서 10행이 됩니다.

ALTER TABLE 수강
  DROP CONSTRAINT
    수강_학생_FK;
ALTER TABLE 수강
  ADD CONSTRAINT
    수강_학생_FK
    FOREIGN KEY (학번)
    REFERENCES 학생 (학번)
    ON DELETE CASCADE;
DELETE FROM 학생
WHERE 학번 = 102;
SELECT 학번,
  과목코드,
  점수
FROM 수강
ORDER BY 학번, 과목코드;

시험 꼴로 적기

시험은 참조 동작을 외래키 뒤에 적는 표준 SQL 꼴로 냅니다. 지울 때는 ON DELETE, 부모의 기본키 값을 고칠 때는 ON UPDATE 뒤에 적습니다.

FOREIGN KEY (학과코드)
  REFERENCES 학과 (학과코드)
  ON DELETE SET NULL
  ON UPDATE CASCADE

이렇게 걸면 학과를 지울 때는 학생의 학과코드가 NULL이 되고, 학과코드 EE를 EL로 고치면 학생의 학과코드도 EL로 따라 바뀝니다. Oracle은 ON DELETE CASCADE와 ON DELETE SET NULL만 받고, ON UPDATE는 받지 않습니다(ORA-02000). 표를 없앨 때 쓰는 DROP TABLE … CASCADE는 다른 것입니다. 둘의 차이는 DDL: CREATE·ALTER·DROP에서 견줍니다.

시험 포인트

• 필기: 위반 사례를 보고 무결성 이름을 고릅니다 — 기본키가 비거나 겹치면 개체 무결성, 외래키가 부모에 없는 값을 가리키면 참조 무결성, 값이 정해 둔 형식·범위를 벗어나면 도메인 무결성입니다. 외래키는 NULL이어도 됩니다. 부모 행을 지울 때 RESTRICT는 막고, SET NULL은 자식의 외래키를 NULL로 바꾸고, CASCADE는 자식 행도 함께 지웁니다.
• 실기: 무결성 이름을 씁니다 — 개체 무결성(Entity Integrity)·참조 무결성(Referential Integrity)·도메인 무결성(Domain Integrity). 참조 동작은 CASCADE·SET NULL·RESTRICT로 씁니다.

확인 문제

1. 과목 표(과목코드, 과목명, 학점, 선수과목)에 다음 네 문장을 하나씩 넣어 봅니다. 넷 모두 막히는데, 그 가운데 도메인 무결성을 어겨 막히는 것을 고르세요.

• ① 과목코드가 NULL인 행을 넣습니다.
• ② 선수과목에 과목 표에 없는 'AL'을 넣습니다.
• ③ 학점 자리에 '세 학점'이라는 글자를 넣습니다.
• ④ 이미 있는 과목코드 'DS'로 새 행을 넣습니다.

2. 어느 쇼핑몰 데이터베이스가 다음 세 가지를 막고 있습니다. (가)~(다)를 막는 규칙의 이름을 한글 이름과 영문으로 함께 쓰세요. 답을 끝까지 쓴 뒤 정답을 펼치세요.

• (가) 주문 표의 수량 자리에 0보다 작은 수를 넣는 일
• (나) 주문 표에 회원 표에 없는 회원번호를 적는 일
• (다) 주문번호를 비워 둔 주문 행을 넣는 일. 주문번호는 주문 표에서 주문 하나를 가리키는 속성입니다.

3. 과목 표의 선수과목은 같은 과목 표의 과목코드를 가리키는 외래키입니다. 지금은 자료구조(DS)를 운영체제(OS)가, 운영체제를 네트워크(NW)가 선수과목으로 둡니다. 과목 행을 지울 때의 요구가 셋입니다. 각각 이 외래키에 적어 둘 참조 동작을 SQL에 적는 낱말 그대로 쓰세요. 답을 끝까지 쓴 뒤 정답을 펼치세요.

• (가) 자료구조를 지우면, 자료구조를 선수과목으로 둔 운영체제 행까지 함께 사라져야 합니다.
• (나) 자료구조를 지우면 운영체제 행은 남고, 운영체제의 선수과목 자리만 비어야 합니다.
• (다) 다른 과목이 선수과목으로 쓰는 과목은 지워지지 않게 막아야 합니다.