관계형 데이터베이스 참조 무결성 제약 조건과 Cascade 옵션의 동작 원리

조금 옛날 이야기(90년대 말~2000년대 초반)이지만,

Primary Key와 Unique Index 관련하여 다음과 같은 논쟁이 있었다고 한다.

 

 

"Primary Key 대신에 Unique Index만을 사용해서 데이터 모델링을 하면

개발자 입장에서 더욱 편리한 데이터베이스 설계를 할 수 있습니다."

 

 

 물론 당연히 PK를 적절하게 사용하여 데이터 모델링을 하는 것이 정석으로 여겨지고 있는 요즘에는 이런 말을 하는 개발자는 없을 것이다. 왜냐하면 Unique Index만을 사용하는 것보다 PK를 적절하게 이용하는 것이 많은 측면에서 유리하다는 것이 수많은 경험으로 이미 검증되었기 때문이다. 딱 하나의 유리함을 꼽자면, Unique Index만을 사용하였을 때에는 참조 무결성 제약조건이 자동 설정되지 않아 데이터베이스의 자체에 불안정성이 발생하지만, PK를 이용할 시에는 이러한 제약조건이 자동으로 설정되어, 데이터베이스의 무결성이 깨지는 실수를 원천 차단할 수가 있다는 것이다.

 

 

제약 조건(Constraints)

 먼저 제약조건(Constraint)이 무엇인지에 대하여 알아보자. 제약조건이라는 것은 한마디로, 관계형 데이터 모델(Relational Data Model)기반의 데이터베이스에 저장되는 데이터들이, 해당 모델의 고유 특성을 유지하기 위하여 데이터 추가 / 삭제 / 정의 시에 반드시 지켜야 하는 제약사항을 의미한다. 즉, 이것이 깨지게 되면 더 이상 해당 데이터베이스 내부에 저장된 데이터들은 관계형 데이터 모델 특성을 지니지 않게 된다. 이러한 현상을 우리들은, 제약사항을 위반하여 무결성이 깨진다고 표현한다.

 

참조 무결성 제약 조건(Referential Integrity Constraint)

 관계형 데이터 모델 기반의 데이터베이스 내에 저장되는 데이터들이 지켜야 하는 제약 조건은 여러가지가 있다. 그 중에 참조 무결성 제약 조건(Referential Integrity Constraint)라는 것이 있다. 이 제약 조건이 무엇을 의미하는 지 다음 예시를 통해 알아보자.

 

데이터베이스 테이블

 위와 같이 Student 테이블과 Team이라는 테이블이 존재한다고 가정해보자.

 그리고 Student테이블의 PK는 '학번' 컬럼, Team테이블의 PK는 '팀 번호' 컬럼으로 설정하였다.

 Student테이블의 팀 번호라는 컬럼은 Team이라는 테이블의 PK를 참조하는 외래 키(Foreign Key, FK)로 설정하였다. 이렇게 하면 우리는 Join이라는 것을 통하여 두 테이블을 연결하여 동시에 조회할 수가 있다. 물론 그 join의 수단이 되는 컬럼은 우리가 Student테이블의 FK로 설정한 '팀 번호' 컬럼이다

 

이 때 Student 테이블의 외래 키(FK)로 설정한 '팀 번호' 컬럼에는, 관계형 데이터 모델의 고유 특성을 유지하기 위한 제약사항(Constraint)이 부과되는데, 이 제약사항을 위반할 시 무결성이 깨지게 된다. 한 마디로 말하면, Student테이블의 '팀 번호'컬럼에 삽입되도록 허용되는 데이터가 이미 정해져 있으며, 허용되지 않는 데이터가 삽입하거나 데이터의 수정 후 결과가 허용되지 않는 데이터일 때 그러한 액션(action)을 허용하지 않는다.

 

같은 Domain을 가져야 한다.

 

 첫 번째 제약 조건은, Student 테이블의 팀 번호 컬럼은 반드시 Team 테이블의 PK와 동일한 도메인을 가져야 한다는 것이다.  쉽게 말하자면, Team의 PK에 삽입 될 수 있는 데이터의 군(群) 내에 속한 데이터만이 Student테이블의 팀 번호 컬럼(FK)에 삽입될 수가 있다는 의미이다.

 

 예를 들어서 학생이 속할 수 있는 팀이 총 세개가 있고, 이들의 팀 번호는 001, 002, 003이라고 해 보자. 이러한 상황에서 Team 테이블의 PK의 도메인(Domain)은 바로 다음과 같다고 말할 수 있다.

 

Domain of Team(Team_NO) = {001, 002, 003}

 

 이 도메인에 속한 001, 002, 003만이

 오직 Student 테이블의 팀 번호 컬럼, 즉 외래키에 해당하는 컬럼에 삽입될 수가 있다는 것이다.

 

 

"도메인(Domain)이라는 것은

특정 속성(Attribute 혹은 컬럼 Column)에 삽입될 수 있는 데이터의 군(群, Group)을 의미한다"

 

 그리고 두 번째 제약 조건이 있다. 사실 상 두 번째 제약조건이 충족된다면 첫 번째 제약조건은 충족되는 것이다. 즉 다음과 같은 명제가 성립하는 것이다.

 

"두 번째 제약조건을 충족한다 → 첫 번째 제약조건을 충족한다"

 

 Student 테이블의 팀 번호 컬럼(FK)에 부가되는 두 번째 제약조건은 바로, Team테이블의 PK에 속하지 않은 데이터를 삽입할 수가 없다는 것이다. 다음 예시를 보자.

 

두 번째 제약조건의 위반

 

 Student테이블의 세 번째 데이터를 보면 004라는 값이 팀 번호 컬럼에 삽입이 되어 있다. 그러나 이 004라는 데이터는 FK 컬럼이 참조하는 테이블의 PK에 속하지 않은 데이터이므로 들어갈 수가 없다. 이러한 경우를 바로, "참조 무결성 제약조건이 위반된 상태"라고 하는 것이다.

 관계형 데이터베이스 관리 시스템에 위와 같은 데이터가 저장될 수가 없다. 위와 같은 데이터를 저장하려는 순간, DBMS에서는 Error를 발생시킬 것이다. MySQL에서는 다음과 같이 SQLSTATE 23000 [Integrity Constraint Violation] 오류가 발생한다.

 

 이로써 참조 무결성 제약조건이 무엇인지에 대하여 알아보았다.

 

"참조 무결성 제약조건(Referential Integrity Contraint)란

다른 테이블의 PK를 참조하는 FK컬럼에 삽입되는 데이터의 종류를 제한하는 제약조건이다.

이 FK컬럼에는 참조하고자 하는 테이블의 PK 컬럼에 존재하는 데이터들만 삽입이 될 수가 있다.

그렇지 않은 데이터를 삽입하거나 수정하는 경우에는 오류가 발생한다."

 

 

 그러면 위와 같이 004라는 데이터를 Student테이블의 팀 번호 컬럼에 삽입하려면 어떻게 해야 할까? 먼저 다음과 같이 Team테이블에 데이터를 삽입하여야 한다.

 

Cascade Option적용

 

 원래는 데이터베이스 관리 시스템(DBMS)에서 데이터를 삭제 / 삽입하거나, 수정한 결과가 참조 무결성 제약조건을 위반한 경우에는 그러한 action을 허용하지 않는다. 그러나 몇 가지 option을 사용하면 여러 경우에 대하여 유연한 처리가 가능한데, 이 때 유용하게 사용되는 것이 바로 cascade option이다.

 

 다음 예를 살펴 보자.

수정 시 참조 무결성 제약조건 위반

 

 4번의 Team에서 숫자 4는 불길하다는 생각이 들어서, 팀의 이름을 5번으로 바꾸기로 하였다. 그래서 위와 같이 Team테이블의 팀 번호를 4번에서 5번으로 바꾸는 쿼리문을 실행하였는데, 실행이 잘 될까? 아마 오류를 발생시키면서 실행되지 않을 것이다.

 왜 실행되지 않을까? 팀 번호를 4번에서 5번으로 변경하게 되면, 이미 4번을 외래 키(FK)를 통해 참조하고 있던 Student컬럼의 세 번째 튜플에서 참조 무결성 제약조건이 위반되기 때문이다. 4번이라는 '실존하지 않는 PK값'을 가지고 있기 때문이다.

 

 그러면 다음 예시를 보자.

데이터 삭제 시 참조 무결성 제약조건 위반

 

 그러면 Team테이블의 4번째 튜플을 삭제하게 되면 어떻게 될까? 본 action에 해당하는 쿼리문을 실행하게 되면 역시 오류가 발생한다. 팀 번호가 4인 튜플이 사라지게 되면, Student테이블의 세 번째 튜플의 FK는 존재하지 않는 PK를 참조하고 있는 것이 되기 때문이다. 이렇듯, 아무런 설정을 해주지 않으면 외래 키를 통해 참조 당하고 있는(Referenced)테이블에 속한 튜플의 PK값을 함부로 수정하지 못한다.

 

 그런데 이 때 cascade ooption을 사용한다면 위의 action이 가능하다. 참조 무결성을 깨뜨리는 action인데 어떻게 허용될수가 있지? 할 수 있다. 하지만 cascade option을 사용하면 위 action을 참조 무결성을 지키면서 실행할 수가 있다. 한번 보자.

 

ALTER TABLE Student ADD CONSTRAINT FOREIGN KEY (팀 번호)
        REFERENCES Team (팀 번호) ON DELETE CASCADE ON UPDATE CASCADE;

 

 위 SQL을 통하여 Delete / Update Cascade 옵션을 설정해 주었다.

 먼저 Update Cascade 옵션이 어떠한 방식으로 동작하는 지 살펴보자.

 

update scacde 동작 과정

 

 위와 같이 FK가 참조하고 있는 Team테이블의 튜플의 PK값이 변하게 된다면, FK의 값도 동시에 변경이 되면서 참조 무결성을 지키게 된다. 이것이 바로 Update Cascade의 동작 방식이다.

 

Delete Cascade의 동작 과정

 

 위와 같이 FK가 참조하고 있는 Team테이블의 튜플이 삭제된다면, 동시에 해당 튜플을 참조하고 있던 Student의 튜플도 덩달아 삭제가 되면서 참조 무결성을 지키게 된다. 이것이 바로 Delete Cascade의 동작 방식이다. 하지만 다음과 같은 방식을 사용할 수도 있다.

 

 

ALTER TABLE Student ADD CONSTRAINT FOREIGN KEY (팀 번호)
        REFERENCES Team (팀 번호) ON DELETE SET NULL ON UPDATE CASCADE;

 

 Delete Cascade가 아닌 Delete SET NULL 옵션을 지정하게 되면,

 외래키를 통해 참조하고 있던 튜플이 삭제되었을 시, 해당 외래키 값을 가지고 있는 튜플을 모두 삭제시키는 것이 아니라 FK에 해당하는 속성만 null로 변화시킨다.

 

DELETE SET NULL의 동작과정