ON DELETE(UPDATE) 옵션

728x90

프로젝트 진행 전 프로젝트 세팅 과정에서 초기 테이블 생성용 schema.sql 을 작성하고 있었다.

테이블 CREATE 쿼리를 작성하며 외래키를 설정하고 요구사항에 따라 ON DELETE, ON UPDATE 옵션을 설정하는데 문득 의문이 들었다.

"나 이게 뭔지 제대로 알고는 쓰나?"

물론 얼추 알기는 안다. 하지만 누군가 "이 옵션이 무슨 옵션이야" 라고 물었을 때 아무 고민 없이 나올 정도로 확실하게 알지는 못한다.

그래서 잠시 반성의 시간을 가졌고, 개념을 확실하게 공부해볼까 한다.

외래 키 (FK, Foreign Key)

외래 키는 데이터베이스 관리 시스템에서 두 테이블 간의 관계를 정의하는 데 사용되는 키이다.

한 테이블의 컬럼이 다른 테이블의 기본 키를 참조하는 것을 의미한다.

주로 참조 무결성을 유지하기 위해 사용되며, 데이터베이스의 일관성과 정확성을 보장한다.

슈퍼 키 : 테이블에서 각 행을 유일하게 식별할 수 있는 속성들의 집합으로, 유일성을 만족한다.
후보 키 : 기본키가 될 수 있는 후보들로, 유일성과 최소성을 만족한다.
기본 키 : 후보키 중 선정된 키로, 유일성과 최소성을 만족한다.
대체 키 : 후보키 중 기본키를 제외한 나머지 키
외래 키 : 다른 테이블이나 릴레이션의 기본 키를 참조하는 속성

ON DELETE, ON UPDATE?

특정 테이블이 변경되었을 때 해당 테이블을 참조하는 외래 키는 어떻게 변경되어야 할까?

이 사항은 요구사항에 따라 다를 것이다. 예를 들어 부서-사원 관계의 테이블이 있다고 치면

부서가 사라지면 소속 사원들은 전부 해고된다.
부서가 사라지면 소속 사원들은 지원팀으로 배치된다.
소속 사원들이 존재하면 부서는 사라질 수 없다.

와 같이 "삭제"라는 하나의 작업에서도 요구사항이 각각 다를 수 있다.

ON DELETE/UPDATE 옵션은 이렇게 외래 키 제약 조건을 거는데 사용된다. 참조된 테이블의 레코드가 삭제/변경될 때 해당 외래 키가 있는 테이블의 레코드에 취할 동작을 정의한다.

이 옵션들은 데이터베이스 내의 데이터 무결성을 유지하기 위해 필수적이며, 참조된 데이터의 변경이나 삭제가 외래 키를 가진 데이터에 미치는 영향을 제어할 수 있다.

옵션의 종류

ON DELETE/UPDATE에 쓰이는 옵션은 아래와 같다.

CASCADE: 참조된 테이블의 레코드가 삭제/수정될 때, 외래 키도 함께 수정되거나 해당 레코드를 참조하는 외래 키를 가진 레코드가 삭제됨
SET NULL: 참조된 테이블의 레코드가 삭제/수정될 때, 외래 키를 NULL로 설정
NO ACTION: 참조된 테이블의 레코드가 삭제/수정될 때 아무런 조치도 취하지 않음. 참조 무결성을 위반할 경우에는 작업이 실패할 수 있다.
RESTRICT: 참조된 테이블의 레코드가 삭제/수정될 때, 해당 레코드를 참조하는 외래 키가 존재하면 작업을 취소
SET DEFAULT: 참조된 테이블의 레코드가 삭제/수정될 때, 외래 키를 기본값으로 설정

NO ACTION의 설명을 보면 바로 이해가 되지 않을 수 있는데, 기본적으로 RESTRICT 똑같다고 생각하면 된다.

참조 무결성을 위반할 경우. 즉, 해당 레코드를 참조하는 외래 키가 있을 경우 작업이 실패하기 때문이다.

ON DELETE / UPDATE를 설정하지 않을 경우 기본적으로 NO ACTION으로 지정이 된다.

실습

이론만 알고 넘어가면 재미가 없으니 실습도 같이 해보자.

아래와 같은 테이블이 있다고 생각하자. 위에서 예시를 든 것처럼 아아아아주 간단한 부서, 사원 테이블 이다.

위 테이블을 생성하기 위해 아래 쿼리를 이용했다.

DB는 MariaDB 11.2.2 버전을 사용했다.

DROP TABLE IF EXISTS EMP;
DROP TABLE IF EXISTS DEP;

CREATE TABLE DEP (
   DEP_NM VARCHAR(20) COMMENT '부서명',
   PRIMARY KEY(DEP_NM)
);

CREATE TABLE EMP (
   EMP_NM VARCHAR(20) COMMENT '사원명',
   DEP_NM VARCHAR(20) COMMENT '부서명',
   PRIMARY KEY(EMP_NM),
   FOREIGN KEY(DEP_NM) REFERENCES DEP(DEP_NM)
   ON DELETE {옵션 입력}
   ON UPDATE {옵션 입력}
);

INSERT INTO DEP VALUES 
   ('A부서'), 
   ('B부서'), 
   ('C부서');
INSERT INTO EMP VALUES 
   ('김씨', 'A부서'),
   ('이씨', 'A부서'),
   ('박씨', 'B부서'),
   ('최씨', 'C부서');

CASCADE

CASCADE는 DELETE/UPDATE 시 외래 키도 함께 UPDATE 되거나 해당 데이터를 참조하는 레코드 자체가 삭제된다.

위에 올려놓은 ON DELETE / UPDATE 옵션에 CASCADE를 넣어 테이블을 생성한 뒤 아래 쿼리를 실행해보자.

UPDATE DEP
SET DEP_NM='AB부서'
WHERE DEP_NM='A부서';

CASCADE는 설명대로 UPDATE 시 외래 키가 함께 변경된다.

위 쿼리가 실행될 경우 김씨, 이씨의 부서가 A부서에서 AB부서로 변경된다.

DELETE FROM DEP
WHERE DEP_NM='AB부서';

DELETE의 경우 해당 레코드를 참조하는 외래 키를 가진 레코드들이 사라지게 된다.

SET NULL

다음으로는 SET NULL 설정이다. 말 그대로 DELETE/UPDATE 시 해당 외래 키가 NULL로 변한다.

실습을 위해 테이블을 생성해보자.

테이블이 생성되었다면 UPDATE 쿼리를 통해 어떻게 변하는지 확인한다.

UPDATE DEP
SET DEP_NM='AB부서'
WHERE DEP_NM='A부서';

DELETE도 마찬가지로 삭제 시 NULL로 레코드가 대체된다.

DELETE FROM DEP
WHERE DEP_NM='C부서';

SET DEFAULT

SET DEFAULT 실습을 하려다가 놀라운 사실을 하나 발견했다.

"MariaDB는 SET DEFAULT를 지원하지 않는다."

아무리 SET DEFAULT로 선언을 해도 RESTRICT로 생성이 되어 왜 이런가 했더니 애초에 지원을 하지 않는 거였다...

어쩔 수 없이 SET DEFAULT 실습은 PostgreSQL (14.5) 에서 진행했다.

아래 쿼리를 이용해 테이블을 생성한다.

DROP TABLE IF EXISTS EMP;
DROP TABLE IF EXISTS DEP;

CREATE TABLE DEP (
   DEP_NM VARCHAR(20),
   PRIMARY KEY(DEP_NM)
);

CREATE TABLE EMP (
   EMP_NM VARCHAR(20),
   DEP_NM VARCHAR(20) DEFAULT '부서없음',
   PRIMARY KEY(EMP_NM),
   FOREIGN KEY(DEP_NM) REFERENCES DEP(DEP_NM)
   ON DELETE SET DEFAULT
   ON UPDATE SET DEFAULT
);

INSERT INTO DEP VALUES 
   ('A부서'), 
   ('B부서'), 
   ('C부서'),
   ('부서없음');
INSERT INTO EMP VALUES 
   ('김씨', 'A부서'),
   ('이씨', 'A부서'),
   ('박씨', 'B부서'),
   ('최씨', 'C부서');

자세히 보면 DEP테이블에 "부서없음"이 추가되었고, EMP.DEP_NM에 DEFAULT가 생성되었다. 만약 DEFAULT로 설정한 값이 DEP 테이블에 존재하지 않으면 에러가 발생하기 때문이다.

이제 UPDATE 쿼리를 돌려보자.

UPDATE DEP
SET DEP_NM='AB부서'
WHERE DEP_NM='A부서';

그러면 사전에 CREATE 쿼리에 정의한대로 DEFAULT값인 "부서없음"으로 변했음을 확인할 수 있다.

이는 DELETE 실행 시에도 마찬가지이다.

DELETE FROM DEP
WHERE DEP_NM='C부서';

RESTRICT

RESTRICT는 말 그대로 제한한다. 해당 레코드를 참조하는 데이터가 존재한다면 UPDATE / DELETE를 제한한다.

실습을 위해 테이블을 생성한다. 다시 MariaDB를 사용했다.

이제 UPDATE를 해보자.

UPDATE DEP
SET DEP_NM='AB부서'
WHERE DEP_NM='A부서';

쿼리 실행 후 데이터를 확인해보면 데이터가 변하지 않았음을 확인할 수 있고, 로그를 확인해보면 아래와 같이 쿼리 실행에 실패했다는 로그를 확인할 수 있다.

ERROR 1451 (23000): Cannot delete or update a parent row: a foreign key constraint fails (`test`.`EMP`, CONSTRAINT `EMP_ibfk_1` FOREIGN KEY (`DEP_NM`) REFERENCES `DEP` (`DEP_NM`))

같은 옵션이므로 이는 DELETE 시에도 똑같이 작동한다.

DELETE FROM DEP
WHERE DEP_NM='A부서';

NO ACTION

앞서 서술했듯 NO ACTION은 사실 RESTRICT이다. 고로 설명은 생략하고 스크린샷 해놓은 것들만 나열해놓겠다.

UPDATE DEP
SET DEP_NM='AB부서'
WHERE DEP_NM='A부서';

DELETE FROM DEP
WHERE DEP_NM='A부서';

728x90

저작자표시 비영리 변경금지

'IT 지식 > 데이터베이스' 카테고리의 다른 글

[DB] 두 개 컬럼을 인덱스로 묶고 그 중 하나로만 쿼리를 하면? (0)	2024.01.16
[PostgreSQL] 컬럼의 Length를 지정해야 하는 이유 (0)	2023.07.05
[DB] 격리수준 (0)	2021.05.30
[DB & BOOT] 트랜잭션 (0)	2021.05.30
[데이터베이스] JSON과 XML (0)	2021.04.06

내가 보려고 만든 블로그

ON DELETE(UPDATE) 옵션

외래 키 (FK, Foreign Key)

ON DELETE, ON UPDATE?

옵션의 종류

실습

'IT 지식 > 데이터베이스' 카테고리의 다른 글

티스토리툴바

ON DELETE(UPDATE) 옵션

외래 키 (FK, Foreign Key)

ON DELETE, ON UPDATE?

옵션의 종류

실습

'IT 지식 > 데이터베이스' 카테고리의 다른 글

관련글

티스토리툴바