論理削除とユニークキー制約

ども。久々に時間ができたのでぼちぼち日記書いてみようかなと。
技術ネタですが。。

データベース設計を行うとき、論理削除を採用することが多いです。
論理削除用のカラムとしては boolean 型もしくは小さい数値型を使います。

create table item (
    id int not null,
    code varchar(40) not null,
    name varchar(240) not null,
    removed boolean not null default false,
    constraint item_pk primary key (id) /* ,
    constraint item_code_uk unique key (code) 本当はユニークキー制約をつけたいが。。 */
);

item（商品）テーブルに一意な code（商品コード）という人が認識しやすい番号を任意に設定できるテーブルを設計するとき、ユニークキー制約をつけてしまうと一度論理削除した商品コードが使いまわせない問題が発生します。仕方なくデータベース側のユニークキー制約は外していました。

このテーブルを使うアプリケーション側では、挿入、更新前にユニークキーチェックを行いますが、アプリケーションサーバが複数台あったり、バッチ処理が平行して実行される可能性があったりでなかなか厳密な整合性はとりづらいです。

最近になってふと、論理削除用のカラムをユニークキー制約に含めてインクリメントすればいいじゃんと思いつきました。そして、主キーが 1つあるタイプならばインクリメントとかしなくてそのまま使えるなーと。

create table item (
    id int not null, -- 0 以上の主キー
    code varchar(40) not null,
    name varchar(240) not null,
    removed int not null default 0,
    constraint item_pk primary key (id) ,
    constraint item_code_uk unique key (code, removed)
);

論理削除するときは、

update item set removed = id where id = ?

通常の検索時には where 句に removed = 0 を指定します。

select * from item where removed = 0

まだ実践で使ってないですが多分大丈夫なはず。
いやースッキリした。