🥝 가끔은 성가신 외래키 제약
외래키 제약 조건을 주고 나서, 해당 제약 조건에 의해서
부모 테이블에서 삭제를 시도할 때 자식 테이블의 참조로 인해서 삭제에 실패하는
경우가 많다. 무결성을 생각하면 이렇게 동작하는 게 당연히 맞지만 가끔은 이게 성가시다.
특히 게시판(부모 테이블)에서 어떤 파일 첨부(자식 테이블)를 하고 있는데,
해당 게시판의 글을 삭제하면 이 첨부된 파일의 정보도 한방에 다 지워지길 바라지만,
앞서 말했듯 외래키 제약 조건 때문에 애플리케이션 코드 상에서
자식 테이블의 정보를 지우고 그 다음에 부모 테이블을 지우도록 코드를 짜야한다.
하지만 이러면 코드가 많이 지저분해진다.
특히 부모 테이블에 자식 테이블이 2개, 3개 늘어나면 더 골치다.
이때 쓸 수 있는 방식이 바로 외래키 제약조건에 on delete 절을 추가하는 것이다!
이제 그 방법을 차차 알아보자.
🥝 일반적인 외래키 제약
일단 시작에 앞서 테스트로 쓸 테이블에 대한 설명과
일반적인 외래키 제약을 걸어주는 쿼리를 가볍게 보고 on delete 절을 알아보겠다.
-- 참고) 외래 키 제약조건의 4가지 규칙
--
-- 1. 부모 테이블이 자식 테이블보다 먼저 생성되어야 한다.
-- 2. 부모 테이블은 자식 테이블과 같은 데이터 타입을 가져야 한다.
-- 3. 부모 테이블에서 참조된 컬럼의 값만 자식 테이블에서 입력 가능하다.
-- 4. 참조되는 컬럼은 모두 프라이머리 키이거나 unique 제약조건 형식이어야 한다.
-- 생성하려는 테이블 목록
-- notice_board : 게시판 테이블 (부모 테이블, 참조되는 테이블)
-- attached_file : 첨부파일 (자식 테이블, 참조하는 테이블)
drop table if exists notice_board;
drop table if exists attached_file;
-- 게시판 테이블
create table notice_board (
notice_id numeric(5) not null primary key, -- 게시판 아이디
notice_dc varchar(60) not null -- 게시판 내용
);
insert into notice_board
values
(1, '게시판 내용1'),
(2, '게시판 내용2'),
(3, '게시판 내용3'),
(4, '게시판 내용4');
-- 첨부 파일 테이블
create table attached_file (
file_id numeric(5) not null primary key,-- 파일 id
file_name varchar(20) not null, -- 파일 이름
notice_id numeric(5), -- 게시판 id
constraint notice_fk foreign key (notice_id)
references notice_board(notice_id)
);
-- notice_id numeric(5) references notice_board(notice_id) 처럼 해도 가능
insert into attached_file values (1, 'file1.xlsx', 1);
insert into attached_file values (2, 'file2.xlsx', 2);
insert into attached_file values (3, 'file3.xlsx', 3);
select * from notice_board;
select * from attached_file;결과적으로 아래와 같은 형태로 데이터가 들어간 테이블이 생성된다.
-- 게시판 테이블
notice_id|notice_dc |
---------+-----------+
1|게시판 내용1|
2|게시판 내용2|
3|게시판 내용3|
4|게시판 내용4|
---------+-----------+
-- 파일첨부 테이블
file_id|file_name |notice_id|
-------+----------+---------+
1|file1.xlsx| 1|
2|file2.xlsx| 2|
3|file3.xlsx| 3|
-------+----------+---------+여기까지가 우리가 아는 아주 일반적인 외래키 제약 조건을 거는 방식이다.
그리고 여기서 아래와 같이 부모 테이블의 특정 row 를 지우려고 하면 참고하고 있는
자식 테이블에 의해서 에러가 난다.
delete from notice_board where notice_id = 2;
-- 에러 내역:
-- 삭제 작업이 "notice_fk" 참조키(foreign key) 제약 조건 -
-- "attached_file" 테이블 - 을 위반했습니다이제 delete on 을 사용해보자.
🥝 DELETE ON
1. 종류
on delete 절은 5가지 종류가 있다.
이중에서 3~5 번만 알아보고 이 글을 끝낼 것이다.
1. on delete no action -- on delete 절을 작성하지 않으면 이게 기본값이다.
2. on delete restrict -- 이건 알아보지 않을 것이다. 트랜잭션과 연관이 있다.
3. on delete cascade
4. on delete set null
5. on delete set default2. on delete cascade
부모 테이블에서 어떤 외래키를 삭제하면, 그 외래키를 참조하는 자식 테이블의
ROW가 모두 삭제되는 기능을 부여해준다.
쿼리를 아래와 같이 짜보고 테스트를 돌려보자.
drop table if exists attached_file;
drop table if exists notice_board;
-- 게시판 테이블
create table notice_board (
notice_id numeric(5) not null primary key, -- 게시판 아이디
notice_dc varchar(60) not null -- 게시판 내용
);
insert into notice_board
values
(1, '게시판 내용1'),
(2, '게시판 내용2'),
(3, '게시판 내용3'),
(4, '게시판 내용4');
-- 첨부 파일 테이블
create table attached_file (
file_id numeric(5) not null primary key,-- 파일 id
file_name varchar(20) not null, -- 파일 이름
notice_id numeric(5), -- 게시판 id
constraint notice_fk foreign key (notice_id)
references notice_board(notice_id)
on delete cascade -- 추가!
);
insert into attached_file values (1, '1번게시판_파일1.txt', 1);
insert into attached_file values (2, '1번게시판_파일2.txt', 1);
insert into attached_file values (3, '1번게시판_파일3.txt', 1);
insert into attached_file values (4, '2번게시판_파일1.txt', 2);
insert into attached_file values (5, '3번게시판_파일1.txt', 3);
select * from notice_board;
select * from attached_file;
delete from notice_board where notice_id = 1;delete 전/후 테이블의 모습은 아래 그림과 같다.
- 부모 테이블의 Tuple이 삭제되고,
notice_id=1을 외래키로 갖던 자식 테이블의 Tuple 들도 모두 삭제되었다.
3. on delete set null
사실 이름만 봐도 알겠지만, 이건 부모 테이블에서 삭제가 일어나면,
참조를 하던 테이블의 외래키 값이 null 로 세팅된다는 의미다.
drop table if exists attached_file;
drop table if exists notice_board;
-- 게시판 테이블
create table notice_board (
notice_id numeric(5) not null primary key, -- 게시판 아이디
notice_dc varchar(60) not null -- 게시판 내용
);
insert into notice_board
values
(1, '게시판 내용1'),
(2, '게시판 내용2'),
(3, '게시판 내용3'),
(4, '게시판 내용4');
-- 첨부 파일 테이블
create table attached_file (
file_id numeric(5) not null primary key,-- 파일 id
file_name varchar(20) not null, -- 파일 이름
notice_id numeric(5), -- 게시판 id
constraint notice_fk foreign key (notice_id)
references notice_board(notice_id)
on delete set null -- 추가!
);
insert into attached_file values (1, '1번게시판_파일1.txt', 1);
insert into attached_file values (2, '1번게시판_파일2.txt', 1);
insert into attached_file values (3, '1번게시판_파일3.txt', 1);
insert into attached_file values (4, '2번게시판_파일1.txt', 2);
insert into attached_file values (5, '3번게시판_파일1.txt', 3);
select * from notice_board;
select * from attached_file;
delete from notice_board where notice_id = 1;delete 전/후 테이블의 모습은 아래 그림과 같다.
- 부모 테이블의 Tuple이 삭제되고, 자식 테이블의 외래키는
NULL로 세팅된다.
4. on delete set default
이건 부모 테이블에서 삭제가 일어나면,
참조를 하던 테이블의 외래키 값이 기존에 설정되어 있던 기본값으로 세팅된다는 의미다.
drop table if exists notice_board;
drop table if exists attached_file;
-- 게시판 테이블
create table notice_board (
notice_id numeric(5) not null primary key, -- 게시판 아이디
notice_dc varchar(60) not null -- 게시판 내용
);
insert into notice_board
values
(1, '게시판 내용1'),
(2, '게시판 내용2'),
(3, '게시판 내용3'),
(4, '게시판 내용4');
-- 첨부 파일 테이블
create table attached_file (
file_id numeric(5) not null primary key,-- 파일 id
file_name varchar(20) not null, -- 파일 이름
notice_id numeric(5) default 1, -- 게시판 id, default 추가!
constraint notice_fk foreign key (notice_id)
references notice_board(notice_id)
on delete set default -- 추가!
);
insert into attached_file values (1, '1번게시판_파일1.txt', 1);
insert into attached_file values (2, '1번게시판_파일2.txt', 1);
insert into attached_file values (3, '1번게시판_파일3.txt', 1);
insert into attached_file values (4, '2번게시판_파일1.txt', 2);
insert into attached_file values (5, '3번게시판_파일1.txt', 3);
select * from notice_board;
select * from attached_file;
delete from notice_board where notice_id = 3;delete 전/후 테이블의 모습은 아래 그림과 같다.
- 부모 테이블의 Tuple이 삭제되고, 자식 테이블의 외래키는 default 값으로 세팅된다.
백엔드를 계속 배우고 있는 개발자입니다 😊