mysql foreign key 지정

출처: http://wory.tistory.com/181

Mysql은 3.23.43 이후 버전부터 지원.

테이블을 생성시 type=innodb 옵션을 주어야 한다. 그러기 위해서는 Mysql 자체에서 innodb를 지원하는지 확인한다.

mysql> show variables like 'have%';

+------------------+-------+
| Variable_name    | Value |
+------------------+-------+
| have_bdb         | YES   |
| have_crypt       | YES   |
| have_innodb      | YES   |
| have_isam        | YES   |
| have_openssl     | NO    |
| have_query_cache | YES   |
| have_raid        | YES   |
| have_symlink     | YES   |
+------------------+-------+

부모 테이블 생성( type=innodb 옵션을 부여)

create table user_info(
no int not null auto_increment,
name varchar(20) not null,
primary key(no)
)engine=innodb;    # 또는 type=innodb;

자식 테이블 생성(type=innodb 옵션 부여 및,, index 및 외래키 설정)

/* 이용자 교육 접수 관리 */
create table user_education(no int not null auto_increment,
user_no int not null,finish int default '1',
finish_no varchar(50),
want text,
index (user_no),    #외래키로 지정할 컬럼을 index로 지정한다.
foreign key(user_no) references user_info(no)
on delete cascade
on update cascade,
primary key(no)
)TYPE=innodb;         #또는 engine=innodb;

Mysql 5.x 버전대에서는 외래키로 지정할 컬럼이 index로 지정이 안되어도 테이블 생성이 되나 그 아래 버전은 인덱스를 지정해야만 외래키로 사용할 수 있다.

*외래키 옵션
on delete cascade : 부모의 해당키가 삭제되면  자동으로 삭제가 된다.
on delete set null : 부모의 해당키가 삭제되면 자동으로 null로 만든다.
on update cascade : 부모의 해당키가 갱신되면 자동으로 갱신된다.


외래키를 사용하여 테이블을 생성하다보면, 부모가 항상 먼저 생성이 되어 있어야 한다거나 참조 무결성등 외래키 제약 조건을 검사한 후에 문제가 없을 시 자식 테이블을 생성하게 되어 있다. 

하지만 다음과 같이 하면 외래키 검사를 하지 않고 생성이 된다.(물론 테이블 구조 및 참조 무결성이 확인된다는 100% 보장이 있다면 사용해야 겟지??)

mysql>set foreign_key_checks=0;   #외래키 검사를 하지 않는 옵션
... 외래키 수행하는 테이블 생성 및 수정 작업 등등등....
mysql>set foreign_key_checks=1;   #검사를 하는 옵션..

* 오류해결
ERROR 1216 (00000): Cannot add or update a child row: a foreign key constraint fails
: 자식 테이블에 외래키로 지정된 컬럼의 값이, 부모에게 없는 값일 때 (참조 무결성 제약 조건)
- 해결 방법 : 외래키 검사를 하지 않거나, 부모에게 없는 값을 갖는 자식 테이블의 컬럼값을 변경 또는 제거하면 된다.