ある日のこと
（施設一覧ページから施設を追加しよ〜っと...）
あれ？一覧に表示されていないのに「既に登録されています」ってエラーが出てくる、なんで？
そういや前に同名の施設登録してあったけど、あれは確か削除したはず...
（テーブル定義を確認してみる）
create table facilities
(
id bigint unsigned auto_increment comment 'ID' primary key,
name varchar(255) not null comment '施設名',
address varchar(255) not null comment '住所',
created_at timestamp not null comment '登録日時',
updated_at timestamp not null comment '更新日時',
deleted_at timestamp null comment '削除日時', # 論理削除
constraint facilities_pk_2 unique (name) # 施設名はユニーク
);
なるほど、論理削除だからユニークキー制約に引っかかってしまうのか...
じゃあ論理削除テーブルにはユニークキー使えないの？
う〜ん...生きているレコードはユニークであって欲しいけど、削除済みは含めたくない。
あ、あと、直接DB操作もするからアプリケーション側での制御だけじゃなくて、ちゃんとDB側で制約もかけたい。
つまり
- 施設名はユニークであるべき
- ただし、論理削除されたレコードはユニークじゃなくてよい
- DBを直接操作することもあるのでDB側で制約をかけたい
ってこと
（謎の声）「できるょ」
あ、あなたは！なまけもの先輩！？
なまけもの先輩「できるょ、ユニークキー制約の特性とGeneratedColumnを使うんだょ」
ユニークキー制約の特性
なまけもの先輩「ユニークキー制約はね、nullには適用できないことを利用するんだょ」
えっ、どういうことですか？
なまけもの先輩「試しにnullableなカラムと、施設名セットの複合ユニークキーを用意してみよう」
create table facilities
(
id bigint unsigned auto_increment comment 'ID' primary key,
name varchar(255) not null comment '施設名',
address varchar(255) not null comment '住所',
null_column tinyint(1) null comment '常にnullのカラム',
created_at timestamp not null comment '登録日時',
updated_at timestamp not null comment '更新日時',
deleted_at timestamp null comment '削除日時',
constraint facilities_pk unique (name, null_column) # 複合ユニークキー
);
なまけもの先輩「そして、施設名に同じ名前を登録するょ」
あれ？登録できる？
なまけもの先輩「じゃあ、今度はnullableなカラムに 1 を設定してさっきみたいに同名の施設を登録してみよう」
あ、ユニークキー制約で弾かれた！
なまけもの先輩「この通りユニークキー制約はnullを無視してしまうんだょ。だってnull同士は同じ値じゃないからね」
💡nullの特性についてもっと知りたい人は↓の記事もみてね！
Generated Columnを使って削除日から「削除された状態」を表す値を自動計算する
ということは、
削除日時が無いときは 削除されていないフラグ に nullを入れ、
削除日時を入れるときに 削除されていないフラグ に 1 を入れればいいってことですね！
な「う〜ん、それだとDB直接操作する人が 削除日時 だけ入れて 削除されていないフラグ を変更してくれない可能性もあるよね？」
それは運用でカバーですよ！（最近覚えた言葉）
な「...」
な「削除日時入れたらフラグが自動で変わる方が安全だと思うんだ。二重管理はバグのもとっていうからね」
な「ついでに 削除されていないフラグ ではなく、 ユニークであるべきか という意味を表す should_unique というカラム名にしようかな。こんな感じで」
create table facilities
(
id bigint unsigned auto_increment comment 'ID' primary key,
name varchar(255) not null comment '施設名',
address varchar(255) not null comment '住所',
should_unique tinyint(1) as (if((`deleted_at` is null), 1, NULL)) comment 'ユニークであるべきか', # Generated Column
created_at timestamp not null comment '登録日時',
updated_at timestamp not null comment '更新日時',
deleted_at timestamp null comment '削除日時',
constraint facilities_pk unique (name, should_unique)
);
な「簡単に説明すると、deleted_atに日時が入ると自動的に1が入るようにしたんだょ。これにより複合ユニークキーが有効になるんだょ」
な「こういった仕組みをGeneratedColumnというんだょ」
な「試しにこの状態でdeleted_atに日付を入れてみよう」
あ、should_uniqueがnullになった！ということは...同じ施設名を登録すると
お〜登録できた！
な「これでやりたいことはできたね」
☑️ 施設名はユニークであるべき
☑️ ただし、論理削除されたレコードはユニークじゃなくてよい
☑️ DBを直接操作することもあるのでDB側で制約をかけたい
ありがとうなまけもの先輩！
な「GeneratedColumnでできることはアイデア次第で色々あるからまた別の機会に紹介するね」
な「ちなみに余談だけど筆者は最近MetaQuest3買ったらしく、皿洗いとかこの記事の執筆もVRでやってるらしいょ。ずっとつけてるもんだから目の周りの跡が消えないって嘆いてた」
へーそうなんだ！（無関心）
まとめ
- nullに対してはユニークキー制約が効かないことを利用する
- データの2重管理を防ぐため、GeneratedColumnで動的に計算する
- 整合性が保たれた状態でDBの運用ができる（重要）
次回もお楽しみに！