InnoDBの複合FOREIGN KEY制約について - 日向夏特殊応援部隊

今回はInnoDBなら是非使いたい機能のひとつ、FOREIGN KEY制約の話です。

まずはテーブルを用意

Fooと言う複合primary keyを持つテーブルを用意したとします。

CREATE TABLE `Foo` (
  `a_id` int(11) NOT NULL default '0',
  `b_id` int(11) NOT NULL default '0',
  `name` text,
  PRIMARY KEY  (`a_id`,`b_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8

こういう場合、このテーブルに対してFOREIGN KEYを張るケースで、
a_id, b_idのセットで張りたい場合があります。

多くの方は専らFooのprimary keyをひとつにしてsequencialな値としてあげて、
そこに単一のFOREIGN KEYを張るんじゃないでしょうか。

ちなみに下記のようなやり方が出来ます。

CREATE TABLE `Bar` (
  `c_id` int(11) NOT NULL,
  `a_id` int(11) default NULL,
  `b_id` int(11) default NULL,
  `name` text,
  PRIMARY KEY  (`c_id`),
  KEY `a_id` (`a_id`,`b_id`),
  CONSTRAINT `Bar_ibfk_1` FOREIGN KEY (`a_id`, `b_id`) REFERENCES `Foo` (`a_id`, `b_id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8

ALTER TABLEによるFOREIGN KEYの設定

MySQL :: MySQL 5.6 リファレンスマニュアル :: 14.6.6 InnoDB と FOREIGN KEY 制約

こちらを見て分かるようにもし上記の例ならば、

mysql> ALTER TABLE Bar ADD FOREIGN KEY (`a_id`, `b_id`) REFERENCES `Foo` (`a_id`, `b_id`) ON DELETE CASCADE ON UPDATE CASCADE;

そして、このFOREIGN KEYを消したい場合は、SHOW CREATE TABLE BarをしてFOREIGN KEYに自動的に割り当てられたKEYの名前(`Bar_ibfk_1`みたいなの)を予め調べておいて、

mysql> ALTER TABLE Bar DROP FOREIGN KEY `Bar_ibfk_1`;

で消す事が出来ます。

トラブルシューティング

InnoDBのFOREIGN KEYを使う場合は、FOREIGN KEYで参照されるテーブルの当該レコードが(必要ならば順番通りに複合)INDEXが張られている必要があります。

これによって、エラーが出るケースが多いです。

ちなみにFOREIGN KEYを張る際に怒られた時はSHOW ERRORSよりもSHOW INNODB STATUSを見ましょう。

例えば、

CREATE TABLE `Hoge` (
  `id` int(11) NOT NULL auto_increment,
  `a_code` int(11) default NULL,
  `b_code` int(11) default NULL,
  `name` text,
  PRIMARY KEY  (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8

と言うHogeテーブルを作ったとしましょう。

CREATE TABLE `Fuga` (
  `id` int(11) NOT NULL auto_increment,
  `a_code` int(11) default NULL,
  `b_code` int(11) default NULL,
  PRIMARY KEY  (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8

でFugaと言うテーブルを作って、a_code, b_codeをHogeテーブルの同じカラムへ複合FOREIGN KEYを張りましょう。

mysql> ALTER TABLE `Fuga` ADD FOREIGN KEY (`a_code`, `b_code`) REFERENCES `Hoge` (`a_code`, `b_code`);
ERROR 1005 (HY000): Can't create table './test/#sql-20c7_4.frm' (errno: 150)

こういう形で怒られます。

ここでSHOW ERRORS, SHOW INNODB STATUSしてみましょう。

mysql> SHOW ERRORS;
+-------+------+----------------------------------------------------------+
| Level | Code | Message                                                  |
+-------+------+----------------------------------------------------------+
| Error | 1005 | Can't create table './test/#sql-20c7_4.frm' (errno: 150) |
+-------+------+----------------------------------------------------------+

でSHOW INNODB STATUSですけど。出力は抜粋。

mysql> SHOW INNODB STATUS\G

------------------------
LATEST FOREIGN KEY ERROR
------------------------
061201 23:58:42 Error in foreign key constraint of table test/#sql-20c7_4:
FOREIGN KEY (`a_code`, `b_code`) REFERENCES `Hoge` (`a_code`, `b_code`):
Cannot find an index in the referenced table where the
referenced columns appear as the first columns, or column types
in the table and the referenced table do not match for constraint.
Note that the internal storage type of ENUM and SET changed in
tables created with >= InnoDB-4.1.12, and such columns in old tables
cannot be referenced by such columns in new tables.
See http://dev.mysql.com/doc/refman/5.0/en/innodb-foreign-key-constraints.html
for correct foreign key definition.

って事なので、ここで複合INDEXを張って見ましょう。

mysql> CREATE INDEX Hoge_IDX1 ON Hoge(a_code, b_code);

で改めてALTER TABLEすると。

mysql> ALTER TABLE `Fuga` ADD FOREIGN KEY (`a_code`, `b_code`) REFERENCES `Hoge` (`a_code`, `b_code`);
Query OK, 0 rows affected (0.25 sec)
Records: 0  Duplicates: 0  Warnings: 0

まとめ

FOREIGN KEYの役割を今さら言うのも何ですけど、複合INDEXを適切に張ると言う行為自体について考えてみると、

複合INDEXを適切に張らねばならない為にINDEXの設計がデータ設計と共に適切に行われる
複合INDEXを使用する事により、FOREIGN KEYを張る側でもそれぞれのKEYでの検索が単独のテーブルで行う事が出来る。

などなど、結構メリットは大きいんじゃないかなと思います。