참조 무결성과 Cascade 옵션

안녕하세요! 조엘입니다!

 

관계형 데이터베이스를 다루다보면 CASCADE를 활용할 일이 많아요. 

참조의 관계를 맺은 데이터베이스를 신뢰성 있는 상태로 유지하기 위함인데요!

CASCADE 옵션이 무엇을 위해 등장했는지, 무슨 역할을 하는지 알아봅시다! 🎈🎈🎈

 

 

*** 참조 무결성 ***

CASCADE를 본격적으로 알아보기 전에 참조 무결성이라는 용어를 알고 갈 필요가 있어요. 

위키피디아에서 정의한 참조 무결성의 정의는 다음과 같아요. 

 

Referential integrity is a property of data stating that all its references are valid. In the context of relational databases, it requires that if a value of one attribute (column) of a relation (table) references a value of another attribute (either in the same or a different relation), then the referenced value must exist.

 

참조 무결성이란 데이터베이스 상의 참조가 모두 유효함을 일컫습니다.

관계형 데이터베이스에서 하나의 속성이 다른 테이블의 속성을 참조하고 있다면, 참조한 해당 속성이 필히 존재해야합니다. 

 

참조 무결성을 통해 Primary Key(PK)와 Foreign Key(FK) 간의 관계가 항상 유효하도록 관리해요. 

따라서 PK를 참조하는 FK가 있다면, 해당 PK는 수정과 삭제가 불가능해요. 

 

예시를 하나 들어볼게요. 다음과 같이 TEAM 테이블과 PLAYER 테이블을 만들어볼게요. 

H2 데이터베이스에서 진행했어요. 

create table TEAM ( 
    id bigint,
    team_name varchar(255) unique,
    primary key(id)
);

create table PLAYER (
    id bigint,
    team_id bigint,
    player_name varchar(255) unique, 
    primary key (id),
    foreign key (team_id) references TEAM (id)
);

 

이제 해당 테이블에 데이터를 추가해볼게요. 

INSERT INTO TEAM (id, team_name) VALUES (1, 'CHELSEA');

INSERT INTO PLAYER (id, team_id, player_name) VALUES (1, 1, 'LUKAKU');
INSERT INTO PLAYER (id, team_id, player_name) VALUES (2, 1, 'KANTE');

데이터 입력 후 데이터베이스

 

참조 무결성을 위해서 "CHELSEA"라는 TEAM_NAME을 가진 레코드의 ID 값은 수정/삭제가 불가능해요. 

해당 ID값을 참조하고 있는 FK가 있기 때문이죠. (PLAYER의 TEAM_ID)

CHELSEA TEAM의 ID 값 수정/레코드 삭제를 시도해보면, 참조 무결성을 위반한다는 에러가 발생해요. 

 

UPDATE TEAM SET id = 2 WHERE team_name = 'CHELSEA';
DELETE FROM TEAM where id = 1;

H2 데이터베이스가 해당 쿼리는 참조 무결성을 어긴다고 알려준다

 

 

*** CASCADE ***

그렇다면 어떻게 해야 TEAM 테이블의 'CHELSEA' 레코드 PK 값을 수정하거나, 레코드를 삭제할 수 있을까요?

 

한가지 방법으로 FK의 참조값을 NULL로 만들어 참조를 모두 끊은 후, 필요한 수정/삭제를 진행해주는 것이에요. 

하지만 해당 방법은 FK에 NOT NULL 제약 조건이 걸려있다면 활용할 수 없고, 꼼꼼하게 모든 쿼리를 날려야하기 때문에 위험한 방법이에요. 

 

이럴 때 사용하는 옵션이 CASCADE 옵션이에요. 

DB의 값을 수정/삭제할 때, 해당 값을 참조하고 있는 레코드 역시 종속적으로 수정/삭제를 가능하게 해줘요. 

예시와 함께 알아볼게요. 

 

[ 수정 - ON UPDATE CASCADE ]

ALTER TABLE PLAYER DROP CONSTRAINT CONSTRAINT_8CD;
ALTER TABLE PLAYER ADD FOREIGN KEY (team_id) REFERENCES TEAM (id) ON UPDATE CASCADE;

다음과 같이 기존에 있던 제약조건을 삭제하고, ON UPDATE CASCADE 옵션을 추가한 FK 제약조건을 넣었어요.  

이제 한번 CHELSEA 팀의 ID를 바꿔봅시다. 

UPDATE TEAM SET id = 2 WHERE team_name = 'CHELSEA';

해당 TEAM을 참조하고 있던 레코드들도 알아서 바뀐 것을 볼 수 있어요. 

이렇게 ON UPDATE CASCADE 옵션을 활용하면 참조 무결성을 유지하며 PK 값을 수정할 수 있어요. 

 

 

[ 삭제 - ON DELETE CASCADE ]

이번엔 삭제인데요. 종속적으로 삭제가 된다는 것은 무슨 뜻일까요?

해당 예시에서는 TEAM 삭제 시, 해당 TEAM을 참조하던 PLAYER들 역시 모두 종속적으로 삭제한다는 뜻이에요. 

직접 확인해봅시다. 

ALTER TABLE PLAYER DROP CONSTRAINT CONSTRAINT_8CD;
ALTER TABLE PLAYER ADD FOREIGN KEY (team_id) REFERENCES TEAM (id) ON DELETE CASCADE;

이번에도 제약조건을 삭제하고, ON DELETE CASCADE 옵션을 추가해 FK 제약조건을 넣었어요. 

이번엔 CHELSEA 팀의 ID를 삭제해볼게요. 

DELETE FROM TEAM where id = 1;

TEAM과 PLAYER 모두 다 삭제된 것을 알 수 있어요. 

CASCADE 옵션을 통해 해당 값을 참조중인 레코드들을 모두 종속적으로 수정/삭제함을 알아봤어요. 

 

 

*** ON DELETE SET NULL ***

사실 참조하던 레코드가 사라진다고 해당 레코드까지 삭제할 필요는 없을 수도 있어요. 

 

위의 상황이 저는 그렇게 느껴지더라고요. 

'팀이 사라졌다고 플레이어까지 모두 삭제할 이유는 없지 않나?'

'그냥 팀이 없는 플레이어로 만들면 되는거 아닌가?'

 

이런 경우 ON DELETE SET NULL 제약조건을 활용할 수 있어요. 

참조하고 있던 레코드가 사라지면 해당 FK를 NULL로 바꿔버리는 옵션이에요. 

 

한 번 살펴볼게요. 

ALTER TABLE PLAYER DROP CONSTRAINT CONSTRAINT_8CD;
ALTER TABLE PLAYER ADD FOREIGN KEY (team_id) REFERENCES TEAM (id) ON DELETE SET NULL;

DELETE FROM TEAM where id = 1;

위와 같이 수정된 것을 알 수 있어요.