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カラムに外部制約キーを設定しています。
これで外部制約キーを設定したテーブルを作ることが出来ました。
この記事が気に入ったらサポートをしてみませんか?