2009-02-12

外部キー + 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)

2009-02-12

InnoDB + foreign key + cascade削除

制約の後ろに on delete cascade 付ければ従属するテーブルも一緒に消えてくれる
制約の後ろに on delete update ...

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) on delete cascade on update cascade
) type=InnoDB;

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.00 sec)

# 被参照テーブル(主のテーブル)を消す
mysql> delete from main;
Query OK, 1 row affected (0.00 sec)

# こっちも消えてる
mysql> select * from sub;
Empty set (0.00 sec)

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.00 sec)

# update する ( cascade update )
mysql> update main name set name="goge" where id = 1;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

# 従属するテーブルも一緒にupdate
mysql> select * from sub;
+-------+---------+
| subid | subname |
+-------+---------+
|     1 | goge    | 
+-------+---------+
1 row in set (0.00 sec)

2009-02-12

foreign key + InnoDB + set null

親の列がdelete/updateされたときに子の列がnullに変更される
- 参照している列にnot nullが入ってると set null 利かないので注意

drop database if exists hoge;
create database hoge;

use hoge;

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

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

2009-02-12

制約 + check

値の範囲をチェックして有効かどうかを判断してくれる

http://okwave.jp/qa3904216.html

# 5.0
# 注1: 現在のところ、整合性(CHECK)制約はサポートされていません。

MySQLは5.1から有効?
- 文法があってるけど、機能としてはちゃんと動いてくれてないぽい

drop database if exists hoge;
create database hoge;

use hoge;

create table main (
       id    int auto_increment primary key,
       point int not null default 0,
       constraint check (point >= 5) 
) ;