웹마당넷 |MySQL의 alter table 명령어 ( foreign key )

MySQL

제 목

MySQL의 alter table 명령어 ( foreign key )

작성자

높이뜬새

등록일

2007-12-31 21:02:29

조회수

25,660

[MySQL] alter table 명령어 ( foreign key )

※ 경고
높이뜬새의 모르는 사람이 없는 팁시리즈 입니다.
이 팁시리즈는 지면낭비라는 항의시에 즉각 중단됩니다.

alter table 명령어를 대충 살펴보는 과정에서 foreign key 부분만 빠져서 없는 실력으로 쪼금 보충하려 합니다. 저번에도 말씀 드린 바 있듯이 제 나름대로의 정리 과정이라 이해해 주시기 바랍니다.

우선 foreign key는 외래키, 참조키등으로 해석되는데요. 참조되는 테이블의 기본키(pirmary key)나, 유니크(unique)제약이 설정된 column 만을 참조하기 때문에 참조키라는 말도 맞습니다. 그리고 본디 자신의 키가 아니고, 외부에서 뽀려온 키이기 때문에 외래키라는 말도 맞습니다. 저는 여기서는 외래키라는 말로 풀어 나가겠습니다. ( 뭐 풀어나갈 실력이나 있나? )

외래키는 데이타베이스의 참조 무결성을 유지하기 위해서 사용됩니다. 즉 데이타베이스 테이블간의 무결성을 보장하기 위해서 사용되는 중요한 수단인데요, 처리과정에서 참조되는 테이블의 값을 한번 더 체크하므로 지나친 외래키 남발은 속도저하를 유발하는 주요 원인이 되기도 합니다. 꼭 필요한 부분에만 참조키를 설정하도록 설계 부분에서 민감하게 생각해 봐야 하겠습니다. ( 복잡한 ERD가 잘 설계 된 데이타베이스 모델링이라는 생각은 버려!!!! )

alter table로 외래키(foreign key) 삭제와 추가를 테스트하기 위해서 다음과 같은 테이블 두개를 만들겠습니다.
MySQL에서 transaction과 외래키(foreign key)를 사용하기 위해서 type은 InnoDB로 만들었습니다.

create table mytable (
id varchar(12) primary key not null,
name varchar(20) not null
) type=InnoDB;

create table subtable (
sub_id varchar(12) primary key not null,
id varchar(12) not null,
constraint fk_id foreign key ( id ) refrences mytable ( id ) on delete cascade
) type=InnoDB;

외래키를 설명하기 위한 아주 심플한 테이블 두개가 만들어 졌습니다. 위 테이블에서는 mytable의 id 컬럼을 참조하여
subtable의 id를 외래키(foreign key)로 만들었습니다.

constraint fk_id foreign key( id ) references mytable ( id ) on delete cascade 이 부분이 foreign key를 설정하는 부분입니다. mytable의 primary key인 id 컬럼을 참조하여 subtable id 컬럼을 외래키(foreign key)로 참조하겠다는 뜻입니다.
뒤에 on delete cascade 는 옵션 구문인데 다음과 같은 뜻을 가지고 있습니다.

on delelete restrict | cascade | set null | no action | set default

위 옵션은 참조당하는 부모 테이블의 컬럼에 삭제되었을때 외래키가 어떻게 동작할 것인가에 대한 옵션입니다.
열심히 참조하고 있는데 홀연히 사라져 버리면 얼마나 당황 스러울까요?

restrict - 참조하는 부모테이블의 column이 삭제되어도 지우지 말고 버텨라.

cascade - 참조하는 부모테이블의 column이 삭제되면 자식 테이블의 column도 모두 삭제하라

set null - 참조하는 부모테이블의 column이 삭제되면 자식 테이블의 column이 모두 null 이 된다

no action - 참조하는 부모테이블의 column이 삭제되도 무시하라.

set default - 참조하는 부모테이블의 column이 삭제되면 지정된 값으로 대체하라.

이제 잘 만들어진 위 두개의 테이블 중에서 자식 테이블인 subtable의 foreign 키를 alter table로 지워야 할 차례입니다.

[테이블에 외래키(foreign key) 삭제하기]

형식) alter table [테이블명] drop foreign key [constraint에 줬던 foreign key 별명]

mysql> alter table subtable drop foreign key fk_id;

위에서는 제약조건으로 줬던 fk_id라는 foreign키를 삭제하고 있습니다.

[테이블에 외래키(foreign key) 추가하기]

형식) alter table [테이블명] add constraint [foreign key 별칭] foreign key ( 외래키를 줄 컬럼명 ) references
[부모테이블명] (참조할 테이블명) [옵션]

mysql> alter table subtable add constraint fk_id foreign key ( id ) references mytable id on delete cascade;

형식만 들여다 보면 무지하게 까다로워 보이네요. 다 제가 무지한 탓입니다. 밑에 예를 들여 다 보면 별것 아니라는 것을 알 수 있을것입니다.

From : 높이뜬새(www.webmadang.net)