外部キー + InnoDB

# http://dev.mysql.com/doc/refman/5.1/ja/innodb-foreign-key-constraints.html

[CONSTRAINT symbol] FOREIGN KEY [id] (index_col_name, ...)
    REFERENCES tbl_name (index_col_name, ...)
    [ON DELETE {RESTRICT | CASCADE | SET NULL | NO ACTION}]
    [ON UPDATE {RESTRICT | CASCADE | SET NULL | NO ACTION}]

• MySQLはInnoDBじゃないと外部キー動かないだす
  • 外部キーはunquie制約ついてないと駄目

drop database if exists hoge;
create database hoge;

use hoge;

create table main (
       id    int auto_increment primary key,
       name  char(5) not null,
       unique key (name)
) type=InnoDB;

create table sub (
      subid int auto_increment primary key,
      subname  char(5) not null,
      foreign key (subname) references main (name)
) type=InnoDB;

• 上のテーブルの従属関係はこう
  • main(name:unique) <======= sub(subname)
  • main(name)が存在しないと、sub(subnmae)は定義できない

# 存在しない外部キーで入れようとすると弾かれる
mysql> insert into sub (subname) values ("Ho");
ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`hoge/sub`, CONSTRAINT `sub_ibfk_1` FOREIGN KEY (`subname`) REFERENCES `main` (`name`))

mysql> insert into main (name) values ( "hoge" );
Query OK, 1 row affected (0.00 sec)

mysql> insert into sub (subname) values ("hoge");
Query OK, 1 row affected (0.01 sec)

# 被参照のレコードを消そうとすると弾かれる
mysql> delete from main;
ERROR 1451 (23000): Cannot delete or update a parent row: a foreign key constraint fails (`hoge/sub`, CONSTRAINT `sub_ibfk_1` FOREIGN KEY (`subname`) REFERENCES `main` (`name`))

# 順番に消すとおk
mysql> delete from sub;
Query OK, 1 row affected (0.00 sec)

mysql> delete from main;
Query OK, 1 row affected (0.00 sec)