【MySQL】Nullable カラムを含めて複合ユニーク制約を設ける方法

レコードの重複登録を回避するために、ユニーク制約を設ける機会は少なくないと思います。複合ユニーク制約を設ける際にハマったポイントの備忘録記事となります。

結論
前提
解説
さいごに

結論

NOT NULL な仮想カラムを新設しましょう
新設したカラムでは、複数のフィールドから定まる一意な値を保持しましょう
新設したカラムに、ユニーク制約を設けましょう

前提

MySQL に限った話ではないですが、Nullable カラムに対して複合ユニーク制約を設けても、フィールド値に NULL を含む新規レコードは登録されてしまいます。

以下、具体例です。

CREATE TABLE users
(
  id     INT             NOT NULL PRIMARY KEY AUTO_INCREMENT,
  name   VARCHAR(50)     NOT NULL,
  gender TINYINT         DEFAULT NULL,
  UNIQUE (name, gender)
);

INSERT INTO users (id, name, gender) VALUES(
  (1, "赤木", NULL),
  (2, "青木", NULL),
);

この場合に、以下クエリ

INSERT INTO users (name) VALUES ("青木");

を実行すると、レコードが追加され、以下の状態となります。

id	name	gender
1	赤木
2	青木
3	青木

このクエリで、レコードが行われないようにすることが目標となります。

もちろん、カラム gender をNOT NULLにできればそれに越したことはありませんが、実際にはそううまくもいきません。

解説

早速ですが、上記具体例での回避策です。結論で書いた操作、

1. NOT NULL な仮想カラムを新設しましょう
2. 新設したカラムでは、複数のフィールドから定まる一意な値を保持しましょう
3. 新設したカラムに、ユニーク制約を設けましょう

をまとめてやると、以下クエリとなります。

ALTER TABLE users ADD COLUMN uniqu_key_text VARCHAR(255) GENERATED ALWAYS AS (
  CONCAT(name, '-', COALESCE(gender, ''))
) STORED NOT NULL UNIQUE KEY;

まず、今回、仮想カラム uniqu_key_text を作成します。別に仮想でなくても問題はないのですが、複数のフィールドから一意に求められるREAD ONLYな値であるため、明示的に保持しておく必要もないかと思います。

このカラムの値には文字列を保存します。CONCAT() 内部では、複合ユニーク制約をかけたいカラム同士から定まる文字列を羅列しています。つまり、「一旦全部を文字列としてしまおう」ということです。注意点としては、uniqu_key_text の文字数に配慮することです。多くのカラムに対して複合ユニーク制約をかけたい場合は、連結する文字列が増えます。文字数の上限を定めるために、md5()、SHA2()などを用いてハッシュ化した値を保持しても良いと思います。

仮想カラムを設定の種類には、VIRTUAL と STORED があります。重複登録を避けたいのであれば、登録時に何度も CONCAT() での演算がされることがないよう、STORED がよいのかと思います。

さいごに

皆様の開発の一助となれば幸いです。本稿のような手軽な記事を増やしたい気もしています。