MySQL :: foreign key 사용하기, on update cascade, on delete set null

MySQL 에서 foreign key 관련해서 실험을 해봤다. 좌측의 Oracle 카테고리에서 foreign key 에 대한 설명은 이미 한 상태이다. 그래도 간략히 설명하면

Foreign key 는 외부 테이블에서 참고하려는 주 키 (primary key) 를 의미한다. 주 키 란, 테이블에서 유일한 값을 가지는 속성을 의미한다. 가령 대학생의 속성은 여려가지가 있을 수 있다. 나이, 학부, 이름, 키, 주소, 학번 등등등.. 여기서 학번은 같은 대학내에서 결코 중복되서는 안된다. 학번은 학생을 구별지어주는 ID 이기 때문이다. 이 때 학번이 학생 이라는 테이블의 주 키가 된다. 

그럼 외부 키(foreign key) 는 무엇을 의미하는가? 학교에서 관리를 편하게 하기 위해 학부의 이름을 이름 그대로 쓰지 않고 코드로 맵핑 시키는 테이블을 만들었다. 그래서 기계과는 101, 컴퓨터공학과는 102, 경제학과는 201, 경영학과는 202 이렇게 코드를 만들었다.

code           name

==============

101           기계과

102       컴퓨터공학과

201          경제학과

202          경영학과

뭐 대충 위와 같은 테이블이 될 것이다. 이 테이블의 이름을 학부 정보니까 school 이라고 하면 이 school 의 주 키(primary key) 는 code 가 될 것이다.

자 그럼 외부 키는 뭘 의미하느냐면,, 다시 학생 테이블로 가보자. 여러 속성들은 시간 및 지면 관계상 생략하고, 주 키 외부 키 이름 이렇게 세 개의 속성을 가지는 테이블로 만들어 보자.

  stu_id          school_code          name

==========================

201101             101                 김감찬

209811             201                 이산수

200330             202                 영심아

207098             101                 이심산

210240             102                 한상첨

209419             201                 도대채

자, 위의 테이블에서 주 키는 stu_id 이고 외부 키는 school_code 이다. 이제 외부키가 무엇인지 감이 왔을 것이다.

오늘 하려는 것은 외부 키로 지정된 테이블의 주 키의 값을 바꿔주면 자연스럽게 외부 키도 바꿔주려는 것이다. 무슨 말이냐면, 지금 기계과 코드가 101 인데, 이 것을 103 으로 바꿨을 때, 학생 테이블에 school_code 에 101 값도 103 으로 바꿔주겠다는 것이다.

혹은, school 테이블의 202 를 지웠을 때, 학생 테이블의 202 에 해당하는 행(영심아 값이 있는 바로 그 행)도 같이 없애주거나, 혹은 201 를 지웠을 때, 학생 테이블의 201 값들은 null 로 바꿔주도록 설정해보자는 것이다.

먼저 실험을 위한 테이블을 만들자. 세 개의 테이블을 만들 것이다. 하나는 아까 설명한 school 테이블이고 다른 두 개는 실험을 위해, 학생 테이블을 나눠서, 쥬니어(3학년) 테이블과, 시니어(4학년) 테이블로 만들것이다.

쿼리문은 아래와 같다.

create table School(

       code int not null primary key,

       name varchar(15)

);

 

create table Junior(

       stu_id int not null auto_increment primary key,

       name varchar(15),

       school_code int,

       foreign key(school_code) references School(code)

       on delete cascade

       on update cascade

);

 

create table Senior(

       stu_id int not null auto_increment primary key,

       name varchar(15),

       school_code int,

       foreign key(school_code) references School(code)

       on delete set null

       on update cascade

);

위의 쿼리문을 실행하고 나서 테이블이 만들어 지고, 아래와 같이 정보를 입력해 주었다.
 

이제 여기서 아까 얘기한대로 기계과 101 코드를 301 코드로 바꿔 보자. 

update school set code=301 where code=101;

이렇게 쿼리문을 실행시키고 결과를 보자. 

school 테이블만 101 에서 301 로 바꿔줬는데, junior 테이블의 기존의 101 값 까지 301 로 바뀐 것을 볼 수 있다.

이 것을 가능하게 한 것이 아까 맨 처음 테이블을 만들 때, 

on update cascade 

를 해줬기 때문에다. 그러므로 주 키를 업데이트 해줬을 때, 이 주키를 외부키로 가지는 값들까지도 같이 업데이트가 된 것이다.

이제는, delete 를 해보자. 그런데 처음 테이블을 만들 때 잘 보면, junior 테이블은 on delete cascade 이고 senior 테이블은 on delete set null 이다. 이 차이가 어떤 것인지 보기 위해 school 테이블의 201 값을 지워보도록 하겠다. 

delete from school where code=201;

이런 쿼리문을 실행하고 결과를 보니 아래와 같다.

on delete cascade 를 해준 junior 테이블은 삭제 되는 외부키에 해당하는 행을 같이 삭제 시켜버리지만, on delete set null 을 해준 senior 테이블은 삭제 되는 외부키를 가지는 값을 null 로 바꿔줬다.

외부 키는 잘만 사용하면 정말 좋은 것 같다. 오늘은 여기까지!

* 참고할 만한 사항.

외부 키가 참조할 수 있는 값은 꼭 primary key 가 아니라도, unique 이면 가능하다.