はじめに
近年、テーブル設計で論理削除を採用することが増えているのではと思っています。
それは、以下の様な事情が増えていると思うからです。
- Web APIのクライアントとしてネイティブアプリ・ハイブリッドアプリを開発している
- クライアント側のデータ更新は、基本的にサーバから取得したデータを元に行っている（サーバが正、クライアントが副 ）
- そのうちの「削除」についても、サーバから取得したデータの削除フラグを元に行っている
こういう事情のもとでは、サーバ側のDBで論理削除を採用するのが一般的なのかなと思います。
論理削除とUNIQUE制約について
※ 以下はMySQLでの経験を元にした話で、他のDBでは当てはまらない話があるかも知れません
論理削除のためのテーブル設計でよく
「emailカラムにUNIQUE制約かけたいが、削除されたユーザに関しては無視したい。削除されているのなら、同じemailのユーザをもう一度作れるべき」
という問題に遭遇すると思います。
解決方法は色々あるようですが、最近僕が使っているSequelizeというNode.js用のORMは論理削除のサポートがあり、DATETIME型のdeletedAtというカラムが使われます。
BOOLEANで非削除・削除を表すのではなく、 削除するときに削除日時を入れるわけです。
これなら、emailとdeletedAtの複合UNIQUEキーを付けてあげれば解決ですね。
- deletedAtが空（「空」については後述）で同じemailのユーザを複数作れないように、UNIQUE制約が効いてくれる
- deletedAtは削除する度に値が違うので、同じemailのユーザを複数回削除してもUNIQUE制約的に問題無い
しかし！これには注意点があります。
deletedAtのデフォルト値（非削除状態を表す値）をNULLに設定してはいけません。
僕はこれでハマりました。
NULLは「値」というより「値が無い状態」という扱いのため、UNIQUE制約に縛られないのです。
emailとdeletedAtの複合UNIQUEキーがあり、deletedAtのデフォルトがNULLだと、emailが1@example.comでdeletedAtが NULLなレコードはガンガン作れてしまいます。。
なので、deletedAtのデフォルトには何か特定の日時を設定します。1970-01-01 00:00:00 (UNIXエポック) が分かりやすくて良いと思います。
やっとSequelizeの話
まさに先日これでハマったわけですが、Sequelize絡みでさらに困った展開となって行きました。
Sequelizeの論理削除サポートでは、「非削除の場合deletedAtはNULL」が前提となっていたのです。
deletedAtのデフォルトをNULL以外に変えても、SELECTクエリでは相変わらずdeletedAt is NULLだったのです。
そして、意を決して相談して修正してpull requestしたところ、取り込んでもらえました。
この記事を書いている時点の最新バージョンv3.14.2では、deletedAtのデフォルトにNULL以外を設定しても論理削除サポートが正常に機能します。
モデルはこんな感じで定義すればOKです：
var User = this.sequelize.define('user', {
email: {
type: Sequelize.STRING,
unique: 'compositeUniqueKey' // 複合UNIQUEキーの指定
},
deletedAt: {
type: Sequelize.DATE,
unique: 'compositeUniqueKey', // 複合UNIQUEキーの指定
defaultValue: new Date(0) // UNIXエポック
}
}, {
paranoid: true, // 論理削除サポート
});
※ 実はv3.9.0でPRを取り込んでもらったのですが、自分のテストコードに漏れがあったため、その後のバージョンで壊されてしまい、v3.14.2でそれを直すPRを取り込んでもらっています。
おわりに
この記事を書く前に論理削除について改めてちょっと検索してみたのですが、かなり色々な議論があり、実はかなり深いテーマなんだなと思いました。
つい最近も、論理削除がテーマの勉強会が開かれていたり。。
これを期に「SQLアンチパターン」読んでみようと思います。