MySQL ON delete , cascade , set null에 대해

delete에 대해서

 

delete는 테이블 내에 레코드를 삭제 할 때 사용하는 명령어다.

 

ID	이름	학번
1	김자바	SUM1
2	이자바	SUM2

학생들의 레코드가 위와 같은 테이블이 있다고 할때 delete를 사용하면

조건에 따라 하나씩 지우거나 모두 지우는 것이 가능하다.

 

하지만 여러개의 테이블이 foreign key로 묶여있는경우

어떤 테이블의 정보가 다른 테이블과 foreign key로 묶여있을때 delete 명령어를 사용하면 에러가 나는데 , 

error 1451 (23000): Cannot delete or update a parent row: a foreign key constraint fails 에러를 자세히 보면

foreign key로 묶여있기 때문에 삭제할 수 없다고 한다.

부모 쪽의 데이터를 먼저 지울 수는 없지만 자식 쪽의 데이터를 지우고 부모 쪽의 데이터를 삭제한다면 가능하다.

 

 

on delete cascade에 대해서

 

cascade란 부모 테이블의 데이터에 delete 또는 update 명령어를 사용할 때 자동적으로 자식 테이블의 매치되는 데이터에도 똑같이 delete 또는 update를 반영하는 것이다.

테이블의 생성시 foreign key constraints 옵션에 on delete cascade를 설정해야 한다.

이 옵션을 사용하면 부모테이블을 삭제할 경우 자동적으로 자식테이블도 삭제되기 때문에 연결된 데이터를 한번에 지울 수 있어서 데이터의 관리가 편리해야지 일관성을 유지할 수 있다.

 

사용예)

drop table if exists board2;
create table board2(
	id bigint, -- 글번호
    board2_writer varchar(20) not null, -- 작성자
    board2_contents varchar(500), -- 내용
    constraint pk_board2 primary key(id)
);

drop table if exists comment2;
create table comment2(
	id bigint, -- 글번호
    comment2_writer varchar(20) not null, -- 댓글 작성자
    comment2_contents varchar(200), -- 댓글 내용
    board2_id bigint, -- 어떤 게시글에 작성된 댓글인지 글번호 정보가 필요함
    constraint pk_board2 primary key(id),
    -- on delete cascade은 부모 테이블이 삭제되면 자식 테이블 값도 삭제되는 것이다.
    constraint fk_comment2 foreign key(board2_id) references board2(id) on delete cascade
);

게시판을 예로 들어 글인 board2 테이블과 댓글인 comment2 테이블을 생성하고 , 

comment2 테이블을 생성할때 on delete cascade를 추가하였다.

-- 게시글 4개 작성
insert into board2(id, board2_writer, board2_contents)
values(1, 'writer1', 'contents1');
insert into board2(id, board2_writer, board2_contents)
values(2, 'writer2', 'contents2');
insert into board2(id, board2_writer, board2_contents)
values(3, 'writer3', 'contents3');
insert into board2(id, board2_writer, board2_contents)
values(4, 'writer4', 'contents4');
select * from board2;
-- 1,2,3번 게시글에 댓글 작성
insert into comment2(id, comment2_writer, comment2_contents, board2_id)
	values (1, 'c writer1', 'c contents1', 1);
insert into comment2(id, comment2_writer, comment2_contents, board2_id)
	values (2, 'c writer1', 'c contents1', 2);
insert into comment2(id, comment2_writer, comment2_contents, board2_id)
	values (3, 'c writer1', 'c contents1', 3);
insert into comment2(id, comment2_writer, comment2_contents, board2_id)
	values (4, 'c writer1', 'c contents1', 4);
select * from comment2;

글 4개와 각 글마다 댓글 1개씩 작성하였다.

-- 3번 게시글 삭제
delete from board2 where id=3;
-- 4번 게시글 삭제
delete from board2 where id=4;

원래라면 묶여있기때문에 삭제가 가능하지 않지만 delete cascade를 사용했기 때문에 오류없이 삭제되는것을 볼 수 있다.

 

 

set null에 대해서

참조되는 부모 테이블에 대한 delete를 허용하고 , 

부모 테이블 값이 삭제되면 해당 참조하는 자식 테이블의 값은 NULL값으로 설정되게 된다.

 

즉 , 개체를 변경/삭제할때 다른 개체가 변경/삭제할 개체를 참조하고 있을경우

참조하고 있는 값은 NULL로 세팅한다는 뜻이다.

 

사용예)

drop table if exists board3;
create table board3(
	id bigint, -- 글번호
    board3_writer varchar(20) not null, -- 작성자
    board3_contents varchar(500), -- 내용
    constraint pk_board3 primary key(id)
);

drop table if exists comment3;
create table comment3(
	id bigint, -- 글번호
    comment3_writer varchar(20) not null, -- 댓글 작성자
    comment3_contents varchar(200), -- 댓글 내용
    board3_id bigint, -- 어떤 게시글에 작성된 댓글인지 글번호 정보가 필요함
    constraint pk_board3 primary key(id),
    -- on delete set null : 자식 데이터는 유지되지만 참조 컬럼은 null로 바꿈
    constraint fk_comment3 foreign key(board3_id) references board3(id) on delete set null
);

 

게시판을 예로 들어 글인 board2 테이블과 댓글인 comment2 테이블을 생성하고 ,

comment2 테이블을 생성할때 on delete set null을 추가하였다.

 

insert into board3(id, board3_writer, board3_contents)
values(1, 'writer1', 'contents1');
insert into board3(id, board3_writer, board3_contents)
values(2, 'writer2', 'contents2');
insert into board3(id, board3_writer, board3_contents)
values(3, 'writer3', 'contents3');
insert into board3(id, board3_writer, board3_contents)
values(4, 'writer4', 'contents4');
select * from board3;
-- 1,2,3,4번 게시글에 댓글 작성
insert into comment3(id, comment3_writer, comment3_contents, board3_id)
	values (1, 'c writer1', 'c contents1', 1);
insert into comment3(id, comment3_writer, comment3_contents, board3_id)
	values (2, 'c writer1', 'c contents1', 2);
insert into comment3(id, comment3_writer, comment3_contents, board3_id)
	values (3, 'c writer1', 'c contents1', 3);
insert into comment3(id, comment3_writer, comment3_contents, board3_id)
	values (4, 'c writer1', 'c contents1', 4);
select * from comment3;

글 4개와 글에 맞는 댓글을 1개씩 설정하였다.

-- 3번 게시글 삭제
delete from board3 where id=3;
-- 4번 게시글 삭제
delete from board3 where id=4;

3번 , 4번 글을 삭제하였다.

3 , 4번에 삭제된 것을 볼 수 있다.

3 , 4번의 참조값이 NULL로 변경되었다.

 

 

 

 

즉 , cascade와 set null의 개념은 똑같이 삭제할 것인가 값을 null로 줄것인가의 차이다.