以下のようなname, emailというカラムを持ったusersテーブルがありemailにユニーク成約がついており、これをSoftDestroyさせたものはユニーク制約の対象外にさせるようにしたかったが、単純にユニーク成約を外すとタイミングによってユニークにならない場合がでてしまうのでちょっと工夫して対応しました。
対応前
テーブル
mysql> SHOW COLUMNS FROM users;
+------------+--------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+------------+--------------+------+-----+---------+----------------+
| id | bigint(20) | NO | PRI | NULL | auto_increment |
| name | varchar(255) | NO | | NULL | |
| email | varchar(255) | NO | UNI | NULL | |
| created_at | datetime | NO | | NULL | |
| updated_at | datetime | NO | | NULL | |
+------------+--------------+------+-----+---------+----------------+
インデックス
mysql> SHOW INDEX FROM users;
+-------+------------+----------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+-------+------------+----------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| users | 0 | PRIMARY | 1 | id | A | 1 | NULL | NULL | | BTREE | | |
| users | 0 | index_users_on_email | 1 | email | A | 1 | NULL | NULL | | BTREE | | |
+-------+------------+----------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
対応後
変更点はSoftDestroy用にavailableカラムを追加したのと、インデックスをemail, availableの複合インデックスに変更しました。
テーブル
mysql> SHOW COLUMNS FROM users;
+------------+--------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+------------+--------------+------+-----+---------+----------------+
| id | bigint(20) | NO | PRI | NULL | auto_increment |
| name | varchar(255) | NO | | NULL | |
| email | varchar(255) | NO | MUL | NULL | |
| available | tinyint(1) | YES | | 1 | |
| created_at | datetime | NO | | NULL | |
| updated_at | datetime | NO | | NULL | |
+------------+--------------+------+-----+---------+----------------+
インデックス
mysql> SHOW INDEX FROM users;
+-------+------------+------------------------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+-------+------------+------------------------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| users | 0 | PRIMARY | 1 | id | A | 0 | NULL | NULL | | BTREE | | |
| users | 0 | index_users_on_email_and_available | 1 | email | A | 0 | NULL | NULL | | BTREE | | |
| users | 0 | index_users_on_email_and_available | 2 | available | A | 0 | NULL | NULL | YES | BTREE | | |
+-------+------------+------------------------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
このように設定しておいて、SoftDestroyする場合はNULLを入れるようにします。そうするとインデックスにNULLは登録されないので、ユニーク制約には引っかからず登録できるようになります。
登録されたデータ
mysql> SELECT * from users;
+----+------+-----------------+-----------+---------------------+---------------------+
| id | name | email | available | created_at | updated_at |
+----+------+-----------------+-----------+---------------------+---------------------+
| 1 | Bob | bob@example.com | NULL | 2019-09-07 10:50:50 | 2019-09-07 10:50:50 |
| 2 | Bob | bob@example.com | NULL | 2019-09-07 10:51:20 | 2019-09-07 10:51:20 |
| 3 | Bob | bob@example.com | 1 | 2019-09-07 10:56:54 | 2019-09-07 10:56:54 |
+----+------+-----------------+-----------+---------------------+---------------------+
有効なデータを取り出すとき
mysql> SELECT * from users WHERE users.email = 'bob@example.com' AND users.available = 1;
+----+------+-----------------+-----------+---------------------+---------------------+
| id | name | email | available | created_at | updated_at |
+----+------+-----------------+-----------+---------------------+---------------------+
| 4 | Bob | bob@example.com | 1 | 2019-09-07 10:56:54 | 2019-09-07 10:56:54 |
+----+------+-----------------+-----------+---------------------+---------------------+