【MySQL】enumの是非について（ハンズオン・動画付き）
今回は、MySQLでenumを使うことの是非について見ていきたいと思います。
動画はこちらです。
なお、こちらの素晴らしい記事を非常に参考にさせていただきました。
では、見ていきましょう。
現時点では、こちらのpersonテーブルを想定しています。
なお、auto_incrementの設定などについてご不明の場合は、前回のnoteをご参照ください。
１ enumのカラムの追加
まずは、enumを追加してみましょう。
これにより、あらかじめ定めたもの以外の入力を防ぐことができます。
alter table person add sex enum ('man', 'woman', 'unknown');下のように性別のsexというカラムを追加しました。
２ データの更新
２ー１ enumに存在する場合
enumに存在する値を元に変更してみましょう。
update person set sex = 'man' where id = 1;id = 1のレコードのsexをmanに設定しました。
２ー２ enumに存在しない場合
仮にsexに存在しない「guy」を設定しようとしたらどうなるでしょう。
update person set sex = 'guy' where id = 2;下のようにエラーとなりました。
３ enumに値を追加したい場合
こちらは通常のカラムを変更する手順で変更することができます。
alter table person modify sex enum('man','woman','unknown','not_answered');下のように、新しく「not_answered」を追加しました。
４ すでに使われている値を変更しようとした時
例えばすでにid=1で使われている「man」を変更しようとするとどうなるでしょうか。
alter table person modify column sex enum('guy','woman','unknown','not_answered');すると、下のようにエラーとなりました。
５ すでに使われている値の修正について
修正するためには次の３つのステップを踏まなければなりません。
一度に変更することができません。
５ー１ enumに変更したい値の追加
まずは次のようにして、変更したい値をenumに追加します。
alter table person modify column sex enum('man','woman','unknown','not_answered','guy');
５ー２ 変更前のenumを持っているレコードを全て更新
次に、変更前のenumを持っているレコードを検索し、全て新しい値に変更します。
update person set sex = 'guy' where sex = 'man';これで、全てのレコードが変わりました。
５ー３ 変更前のenumの値を削除
最後に、変更前のenumの値である「man」を除いて更新します。
alter table person modify column sex enum('woman','unknown','not_answered','guy');
以上の３ステップで変更することができます。
６ 外部キーによる実装
では、enumを用いずに、外部キーで行った場合はどうなるでしょう？
６ー１ そもそも外部キーとは？
こちらがわかりやすいかと思います。
今回であれば「sex」に入る値は必ず「sex」テーブルから取ってくるという制約をつけます。
６ー２ マスターテーブルとトランザクションテーブル
ちなみに、今回のsexテーブルやpersonテーブルなどをマスターテーブルと呼びます。
こちらがわかりやすいと思います。
６ー３ 参照先のテーブルを作成する
では、まずは参照先となるテーブルを作っていきましょう。
create table sex(type varchar(20), primary key(type));下のように、sexテーブルができました。
６ー４ レコードの一括インサート
「insert into」でレコードを追加していきます。
今回はせっかくなので、複数レコードを一括インサートします。
では、やっていきましょう。
insert into sex (type) values ('man'),('woman'),('unknown');次のように、一括インサートができました。
ちなみに、一つでもおかしい値があれば、全て失敗となります。
insert into sex (type) values ('man'),('woman'),(11111111111111111111111111);
６ー５ 外部キーの接続を行う
では、参照先のテーブルができましたので、接続していきましょう。
まずはpersonテーブルにカラムを作成します。
alter table person add sex varchar(10);変更の場合はこちらです。
alter table person modify sex varchar(10);
では、下のコマンドで外部キーをsexカラムに設定していきましょう。
alter table person add foreign key (sex) references sex (type)；これでsexテーブルのtypeカラムに参照できるようになりました。
ちなみに、「MUL」とはなんでしょう。
こちらは外部キー制約があるカラムのうち、重複が可能なものです。
確かに、性別なので重複が可能ですね。（manのレコードなどはたくさんできるはず）
６ー６ 外部キーを設定してみよう
では、実際に外部キーを設定してみましょう。
update person set sex = "man" where id = 3;下のように、テーブル内にある値であれば、設定することができました。
一方、存在しないものを設定しようとするとどうなるでしょう。
update person set sex = "guy" where id = 4;「guy」はsexテーブルに存在しないため、外部キー制約エラー（a foreign key constraint fails）となりました。
ところで、constraintの次に書いてある、`person_ibfk_1`ってなんでしょう？
６ー７ テーブルの構成を見てみよう
では`person_ibfk_1`を知るためにテーブルを見てみましょう。
show create table person;下のようになりました。
今回の外部キー制約の名前を
と名付けているようです。
そして、今回のエラーは「person_ibfk_1」という外部制約のルールに反するので発生しているようです。
（sexテーブルにないものを持ってこようとしていました）
６ー８ sexテーブルの名前を変えるとどうなる？
では、マスターテーブルの「man」を「guy」に変えようとするとどうなるでしょう？
なお、manはすでにpersonテーブルのid=3で使われています。
update sex set type = "guy" where type = "man";下のように外部キー制約エラーが発生しました。
マスターテーブルは削除も更新もできないとなっています。
ここで削除と更新を切り分けて考えたいと思います。
確かにマスターテーブルの「man」が消えてしまったら参照先がなくなって困ってしまいます。
ただ、マスターテーブルの名前の変更が他のテーブルに波及されれば良いケースもあるかもしれません。
７ on句（結合条件）を見てみよう
テーブルを結びつけるときの条件を規定するのがon句になります。
７ー１ on update句について
on update句は片方のテーブルがアップデートされた時に、結合されたテーブルにどのように影響するのかを規定します。
７ー１ー１ 現在の外部キーの削除
まずは、現在存在している外部キーを削除します。
show create table person;上のコマンドで、外部キーを確認し、次のコマンドで削除します。
alter table person drop foreign key `person_ibfk_1`;
７ー１ー２ on updateの設定種別について
次のように設定可能な値は５つあります。
そして、デフォルトではrestrictが設定されているようです。
今回は、更新が反映されるようにするため、「cascade」を使っていきます。
７ー１ー３ on update cascadeの設定について
次のようにして、外部キーを追加します。
alter table person add foreign key (sex) references sex (type) on update cascade;
下のようにうまく設定ができたようです。
７ー１ー４ 更新の反映を確認してみる
では、sexテーブルの更新がpersonテーブルのレコードに反映されるかを見てみましょう。
update sex set type = "guy" where type = "man";下のように、うまく反映されたようです。
７ー２ on delete句について
では、削除が行われたときの挙動についてのon delete句を見てみましょう。
７ー２ー１ cascadeを設定したらどうなるの？
今回も前回と同様、「cascade」を設定してしまうどうなってしまうでしょうか。
alter table person add foreign key (sex) references sex (type) on update cascade on delete cascade;下のように、設定してみました。
ではsexテーブルの「guy」を消してみようと思います。
意図としては、「sex」テーブルだけ消したいと仮定します。
delete from sex where type = 'guy';
しかし、意図通りにならず、「guy」を消したことで、personテーブルの「guy」が設定されていたレコードも消えてしまいました。
今回の場合、せめてsexテーブルの「guy」の削除を行う際に、エラーが発生すれば事前に防ぐことができたかもしれません。
７ー２ー２ on delete restrictについて
そのため、今回は下のrestrictを設定したいと思います。
これにより、「guy」を削除しようとした場合、エラーが発生します。
まずは、事前準備として、下のように、「man」がついたレコードをpersonテーブルに作ります。
insert into sex (type) values ('man');insert into person (first_name, sex) values ('taro', 'man');
７ー２ー３ on delete restrictを設定する
では、on delete restrictを設定してみましょう。
restrictはデフォルトですが、今回は勉強用にあえて設定してみます。
alter table person add foreign key (sex) references sex (type) on update cascade on delete restrict;restrictはデフォルト値のため、特に表示はされていなさそうです。
７ー２ー４ 削除をしてみる
では、削除をしようとして、適切にエラーが出ることを確認しましょう。
delete from sex where type = "man";下のように、personテーブルでレコードで設定されている「man」を消そうとしてみます。
このように期待通り、エラーが発生しました。
今回は以上にいたします。
サポートをしていただけたらすごく嬉しいです😄 いただけたサポートを励みに、これからもコツコツ頑張っていきます😊