『売上猫くん on MySQL』開発日記 - 番外9 - 外部キー制約設定でのたうちまわる

システム開発、運用に様々なトラブルは付き物で、ストレスの元。　特に「マニュアル通りにやってるはずなのにできない、直らない」というのは凹む。　それでも最近は「トラブルも経験のうち」、「(自社の)ナレッジベースに入れてノウハウにしよう」、「ブログに書いて世の中に貢献しよう(笑)」とか、達観しはじめた。が、あまり長い時間、しょんもないことで時間を浪費すると、凄まじく凹む。

さて、今回は外部キー制約の設定について。ここでも凹んだというか、疲弊した。FileMakerのリレーションのところでそれらしきことは簡単にできる。　ただ、このシステムは折角MySQLをデータベースとして使うのだから、PHPやフレームワーク等の多言語、他環境での開発も視野に入れている（というより、実は、当初はFileMaker よりもCakePHPでのWeb開発が先行していたが、大人の事情で優先順位を変更した）。　であれば、MySQL側でできることはできるかぎりMySQLに任せた方が、開発効率が（システムのパフォーマンスも）上がる。　閑話休題。


データが入力されているテーブルに外部キーを設定しようと以下を実行。

alter table neko.estimatedtls
add foreign key estimatedtls(estimateId) references estimates(ID)
ON DELETE cascade ON UPDATE NO ACTION
,add index estimateId (estimateId);


いろいろシンタクスを弄ってみたが駄目。　そこでデータが無い同様のテーブルを作ってやってみると旨くいく。どうやら既存のデータに問題があるらしいことがわかったので、テーブルを一旦空にして外部キー制約を設定、データを取り込み直そうとするとエラーが出る。　MySQL有名某サイトにそれらしい記事を発見。　苦節２日、どうにか原因は分かった。親テーブルの参照キー（ID）にない外部キー（estimateID）が子テーブルにあることが原因らしい。　そこで、そのはぐれ者の子レコードを削除しようと以下を実行。

delete from estimatedtls where
(select ID from estimatedtls where estimateId not in
(select ID from estimates))

と、「Error Code : 1093 You can't specify target table 'estimatedtls' for update in FROM clause」と出る。 どうもこういうサブクエリはUpdataやDeleteでは使用できないらしい。しかたなく、サブクエリの結果をテンポラリテーブルに一旦納めて、それをwhere句に使用することにした。

create temporary table temp(id int);
insert into temp select ID from estimatedtls
where estimateId not in (select ID from estimates);
delete from estimatedtls where Id in (select id from temp)；

結果、成功。　親テーブルに属さないはぐれ者の子レコードを削除できた。　そこで、上記の Alter table以下を再実行すると、めでたく外部キー制約を設定できた。　これにより、FileMakerのリレーションでの設定に関わりなく、見積を削除すると見積明細も自動的に削除されるようになった。
以上、めでたし。

追記（10/08/23）
MySQL Workbench 5.2.16 Beta のGUIを利用して外部キー制約を設定しようとすると、［Column］でフィールドのチェックボックスをチェックできないことがある。　どうもWorkbenchのバグのようだ。　この場合はしょうがないので、外部キーのSQL文を書いて実行すること。　尚、Workbenchの最新版では解消している可能性も。


関連リンク：『売上猫くん on MySQL』開発日記の記事一覧