削除日時を利用した論理削除とユニーク制約を両立させる方法

前提

MySQL
- 部分インデックス機能はない
- ユニーク制約はNULLを対象外にする
削除日時を利用した論理削除の場合の話
- 未削除: deleted_at IS NULL
- 削除済み: deleted_at IS NOT NULL

CREATE TABLE IF NOT EXISTS `users` (
    `user_id` INT NOT NULL AUTO_INCREMENT COMMENT 'ユーザID',
    `name` VARCHAR(191) NOT NULL COMMENT 'ユーザ名',
    `email` VARCHAR(191) NOT NULL COMMENT 'メールアドレス',
    `password` VARCHAR(191) NOT NULL COMMENT 'パスワード',
    `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '作成日時',
    `updated_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '最終更新日時',
    `deleted_at` DATETIME NULL DEFAULT NULL COMMENT '削除日時',
    PRIMARY KEY (`user_id`)
) COMMENT = 'ユーザ情報テーブル'
;

やりたいこと

削除日時を利用した論理削除とユニーク制約を両立させたい

やり方

削除日時を元に、

未削除: NULL
削除済み: 1

になるようなGenerated Columnを追加し、そのGenerated Columnを複合ユニーク制約に含める。

ALTER TABLE `users`
    ADD COLUMN `exists` TINYINT(1) AS (IF(`deleted_at` IS NULL, 1, NULL)) STORED NULL COMMENT '削除されていないレコードをユニークにするための自動生成カラム' AFTER `deleted_at`,
    ADD UNIQUE `uk__users__email__exists` (`email`, `exists`)
;

CREATE TABLE IF NOT EXISTS `users` (
    `user_id` INT NOT NULL AUTO_INCREMENT COMMENT 'ユーザID',
    `name` VARCHAR(191) NOT NULL COMMENT 'ユーザ名',
    `email` VARCHAR(191) NOT NULL COMMENT 'メールアドレス',
    `password` VARCHAR(191) NOT NULL COMMENT 'パスワード',
    `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '作成日時',
    `updated_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '最終更新日時',
    `deleted_at` DATETIME NULL DEFAULT NULL COMMENT '削除日時',
    `exists` TINYINT(1) AS (IF(`deleted_at` IS NULL, 1, NULL)) STORED NULL COMMENT '削除されていないレコードをユニークにするための自動生成カラム',
    PRIMARY KEY (`user_id`),
    UNIQUE KEY `uk__users__email__exists` (`email`, `exists`)
) COMMENT = 'ユーザ情報テーブル'
;

動作確認

[17:07:03] root@127.0.0.1:sample
> INSERT INTO `users`
>     (name, email, password, deleted_at)
> VALUES
>     ('未削除ユーザ', '[email protected]', 'password', NULL),
>     ('削除済みユーザ', '[email protected]', 'password', '2022-08-16 12:00:00')
> ;
Query OK, 2 rows affected
Time: 0.018s

[17:08:31] root@127.0.0.1:sample
> INSERT INTO `users`
>     (name, email, password, deleted_at)
> VALUES
>     ('削除済みユーザと同じメールアドレス', '[email protected]', 'password', NULL)
> ;
Query OK, 1 row affected
Time: 0.003s

[17:08:53] root@127.0.0.1:sample
> INSERT INTO `users`
>     (name, email, password, deleted_at)
> VALUES
>     ('未削除ユーザと同じメールアドレス', '[email protected]', 'password', NULL)
> ;
(1062, "Duplicate entry '[email protected]' for key 'uk__users__email__exists'")