지금 인턴하는 회사에서는 JPA보다는 MyBatis를 쓰도록 한다. 그렇기에 참조 무결성을 유지하기 위해 DB툴로 설정을 많이 하게 되는데 과정을 진행하면서 느꼈고, 하면 좋은 설정들을 써보고자 한다.
관계형 데이터베이스를 다루다보면 CASCASDE를 활용할 일이 많다.
- 참조의 관계를 맺음 데이터베이스를 신뢰성 있는 상태로 유지하기 위함이다.
😎 참조 무결성
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.
😊 참조 무결성 이란, 데이터베이스 상의 참조가 모두 유효함을 일컫는다.
관계형 데이터베이스에서 하나의 속성이 다른 테이블의 속성을 참조하고 있다면, 참조한 해당 속성이 필히 존재해야 한다.
😊 참조 무결성을 통해 PK와 FK간의 관계가 항상 유효하도록 관리한다.
따라서 PK를 참조하는 FK가 있다면, 해당 PK는 수정과 삭제가 불가능하다.
예시
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)
);- 위와 같이 TEAM 테이블과 PLAYER 테이블을 만든다면...
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;😁 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 제약조건을 넣었다.
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;위와 같이 삭제될 수 있따 !!
