📑
nullは不明値なのでnullをもとに一意制約をかけられない
始めに
レコードの有効期間を表示したいときに、start_at, end_atのカラムを用いて表現していました。そして、end_atがnullの場合にアクティブなレコードとして表現しようとしていました。しかし、このやり方ではデータの管理方法に失敗するとアクティブなレコードが複数できてしまう可能性があります。
そのため、アクティブなレコードを1つだけに絞りたかったので、別の主キー + end_atでアクティブなレコードであること表現しようとしました。
しかし、その方法がうまくいかなかったので、同じ轍を踏まないようにブログに残しておきます。
環境
- MySQL
- 8.0
- PostgreSQL
- 17.2
前提
ユーザー間の関係を表現するために、user_id_1, user_id_2とstart_at, end_atで有効期間を表示するテーブルです。
- user_id_1
- user_id_2
うまくいかない原因
DBにおいてnullは「存在しない値」ではありません。nullは「不明」な値です。そのため、nullをもとにハンドリングしようとしてもAレコードのnullとBレコードのnullは一致していると判断されず、nullを使用するとアクティブレコードが1つだけという判定ができません。
実装
PostgreSQLの場合
nullの場合に一意制約を作用させる書き方ができるので、次のように書いてください。
CREATE TABLE relation_timelines (
id SERIAL PRIMARY KEY,
user_id_1 INTEGER,
user_id_2 INTEGER,
start_at TIMESTAMP,
end_at TIMESTAMP,
CONSTRAINT uq_user_ids_start_end UNIQUE (user_id_1, user_id_2, start_at, end_at)
);
CREATE UNIQUE INDEX idx_relation_timelines_unique
ON relation_timelines (user_id_1, user_id_2, (end_at IS NULL))
WHERE end_at IS NULL;
MySQLの場合
MySQLの場合はUNIQUEにするための仮想カラムを追加してください。注意点としては、仮想カラムを0にすると有効なレコード、無効なレコードを1つずつしか許容しないので注意してください。
create table relation_timelines
(
id int auto_increment primary key,
user_id_1 int null,
user_id_2 int null,
start_at datetime null,
end_at datetime null,
end_at_flag tinyint as (if((`end_at` is null), 1, NULL)),
constraint uq_user_ids_start_end
unique (user_id_1, user_id_2, start_at, end_at),
constraint uq_user_ids_start_end_flag
unique (user_id_1, user_id_2, end_at_flag)
);
ソースコード
- なし
終わりに
nullが不明な値であることを今回の失敗で気付けました。MySQLの場合には仮想カラムを使用する一手間があるので、ちょっとたいへんですね。
Discussion