概要
データベーステーブルの物理設計を行うとき、論理削除とユニークキー制約を両立させるための TIPS です。
基本形
create table user (
id int not null,
email varchar(40) not null,
password_sha256 varchar(64) not null,
removed int not null default 0, -- 論理削除カラム
constraint pk primary key (id)
);
メアドとパスワードでログインできる user テーブルを例とします。
removed = 0 が有効なレコードです。
退会時には removed = 1 に更新します。
-- 入会
insert into user (id, email, password_sha256)
values (1, 'foo@example.com', '**dummy**');
-- ログイン removed = 0
select * from user where removed = 0 and email = 'foo@example.com';
-- 退会 removed = 1 で更新
update user set removed = 1 where id = 1;
email にユニークキー制約を付ける
drop table user;
create table user (
id int not null,
email varchar(40) not null,
password_sha256 varchar(64) not null,
removed int not null default 0, -- 論理削除カラム
constraint pk primary key (id),
constraint uk_email unique key (email) -- ユニークキー制約
);
email にユニークキー制約をつけてしまうと、一度退会した会員が同じメアドで再度入会することができなくなってしまいます。
-- 入会
insert into user (id, email, password_sha256)
values (1, 'foo@example.com', '**dummy**');
-- ログイン removed = 0
select * from user where removed = 0 and email = 'foo@example.com';
-- 退会 removed = 1 で更新
update user set removed = 1 where id = 1;
-- 再入会
insert into user (id, email, password_sha256)
values (2, 'foo@example.com', '**dummy**'); -- 重複エラー！
そこで
drop table user;
create table user (
id int not null,
email varchar(40) not null,
password_sha256 varchar(64) not null,
removed int not null default 0, -- 論理削除カラム
constraint pk primary key (id),
constraint uk_email unique key (email, removed) -- removed を含めたユニークキー制約
);
email と removed の複合ユニークキー制約にします。
退会時には removed = 1 ではなく、removed = id で更新するのがキモです。
-- 入会
insert into user (id, email, password_sha256)
values (1, 'foo@example.com', '**dummy**');
-- ログイン removed = 0
select * from user where removed = 0 and email = 'foo@example.com';
-- 退会 removed = id で更新
update user set removed = id where id = 1;
-- 再入会
insert into user (id, email, password_sha256)
values (2, 'foo@example.com', '**dummy**'); -- 成功
この仕組みなら何度でも同じメアドで入退会を繰り返すことが可能です。
+----+-----------------+-----------------+---------+
| id | email | password_sha256 | removed |
+----+-----------------+-----------------+---------+
| 1 | foo@example.com | **dummy** | 1 |
| 2 | foo@example.com | **dummy** | 2 |
| 3 | foo@example.com | **dummy** | 0 | ← このレコードのみが有効
+----+-----------------+-----------------+---------+
データベース
例で使用した sql は mysql でも postgresql でも動作するようにしましたが、mysql なら id は auto_increment を使うと良いでしょう。
なおこの記事は http://blog.utils.jp/2009/07/blog-post.html の書き直しとなります。