MySQLで外部制約キーを設定する

とりあえずアンチョコ。

# master_accountsテーブルを作る
CREATE TABLE `master_accounts`(
`account_id` smallint(5) unsigned NOT NULL AUTO_INCREMENT,
`user_name` varchar(20) NOT NULL,
PRIMARY KEY (`account_id`)
)

# master_operationsテーブルを作る
CREATE TABLE `master_operations`(
`operation_id` smallint(3) unsigned NOT NULL AUTO_INCREMENT, 
`operation_name` varchar(20) NOT NULL,
PRIMARY KEY (`operation_id`),
)
# ※operation_nameについては、idテーブルとnameテーブルを別に作り、
# 中間テーブルを作って名前が変更できるようにしておく実装もあり。今回は簡略化。

CREATE TABLE `action_operation` (
 `operation_action_id` int unsigned NOT NULL AUTO_INCREMENT,
 `account_id` smallint(5) unsigned NOT NULL,
`operation_id` smallint(3) unsigned NOT NULL,  
PRIMARY KEY (`hiraoki_action_id`),
KEY `fk_account` (`account_id`),
CONSTRAINT `fk_account` FOREIGN KEY (`account_id`) REFERENCES `master_accounts` (`account_id`),
CONSTRAINT `fk_operation` FOREIGN KEY (`operation_id`) REFERENCES `master_operations` (`operation_id`),
)COMMENT='操作履歴';

外部制約キーって何

「カラムの中に変な値が入ってこないように、このカラムには、別のテーブルで決めておいた値しか入れちゃダメよ」というルールのことを、「外部制約キー」と呼びます（ざっっっっっくり）

※図はあくまでもざっくりしたイメージです※

こうしておくことで、データベースに変なデータが入ってしまうのを防ぐことが出来て、データの整合性がとれます。

その代わり、ガッチガチに作ってあると、新しいPCに環境つくろー、とか、テストデータをぱぱっと作りたーい、とか思って、稼働中のDBからテスト用DBにデータをコピペっとしたときに、「外部制約キーエラーだよ！」「こっちも外部制約キーエラーだよ！」「こっちも！」「こっちも！！」って芋づる式にエラーが出てしまって、とんだ罰ゲームを味わう羽目になるので、テストデータを手っ取り早く用意する方法は考えておいた方がいいです。
（だから外部制約キーは使わずにDB設計しよう、という宗派もあるようです。チーム開発の場合はチームの宗派に合わせるのが無難です）

外部制約キーを設定する

まず、上図で言う右側のDB（問い合わせを受ける側のDB）を作っておきます。サンプルコードで言う、master_accountsとmaster_operationです。

# master_accountsテーブルを作る
CREATE TABLE `master_accounts`(
`account_id` smallint(5) unsigned NOT NULL AUTO_INCREMENT,
`user_name` varchar(20) NOT NULL,
PRIMARY KEY (`account_id`)
)

このとき、必ず各テーブルでPRIMARY KEY（主キー。変更不可で、NOTNULLで、必ず一意になる値が入っているカラム。自動採番されるidを使用する事が多い）を設定しておきます。

`account_id` smallint(5) unsigned NOT NULL AUTO_INCREMENT,

例えばmaster_accountsでは、account_idを主キーにしたいので、NOT NULLに設定して、AUTO_INCREMENT（自動採番）に設定しています。

PRIMARY KEY (`account_id`)

この部分で主キーを設定しています。

もちろん、各種マスタデータは既にあることも多いので、「このテーブル使って外部制約キーつーくろ」でもOKです。テーブル名と主キーがどれかを確認しておきます。

続いて本命のテーブルを作ります。

CREATE TABLE `action_operation` (
 `operation_action_id` int unsigned NOT NULL AUTO_INCREMENT,
 `account_id` smallint(5) unsigned NOT NULL,
 `operation_id` smallint(3) unsigned NOT NULL,
 `created_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
 PRIMARY KEY (`hiraoki_action_id`),
 KEY `fk_account` (`account_id`),
 CONSTRAINT `fk_account` FOREIGN KEY (`account_id`) REFERENCES `master_accounts` (`account_id`),
 CONSTRAINT `fk_operation` FOREIGN KEY (`operation_id`) REFERENCES `master_operations` (`operation_id`),
)COMMENT='操作履歴';

外部制約キーを設定したいカラムの、型や桁数、数値ならunsigned（正の整数しか入らない）かどうか、など、参照先テーブルと全く同じ設定にします。（もちろん、自動採番はしようがないので外します）

そして、

CONSTRAINT `fk_account` FOREIGN KEY (`account_id`) REFERENCES `master_accounts` (`account_id`),

で外部キー制約を設定します。 ​書式は

CONSTRAINT `制約名` FOREIGN KEY (`制約するカラム名`) 
  REFERENCES `参照先テーブル` (`参照先テーブルの主キーカラム名`),

複数のカラムに外部制約キーを設定することも可能です。サンプルコートではaccount_idとoperation_idの2カラムに外部制約キーを設定しています。

これで外部制約キーを設定したテーブルを作ることが出来ました。