2026.10.06
- 設計
論理削除と物理削除の違いと設計｜削除フラグのデメリットと代替
論理削除は、行を残したまま「削除済み」の印を付ける方式で、物理削除は、DELETE 文で行そのものを消す方式です。論理削除は復元や過去データの参照がしやすい反面、一意制約・検索条件・データ量に手当てが要ります。物理削除は表がすっきり保てる反面、消した行は表からは戻せません。その中間に、削除した行を別の表へ移す「履歴テーブルへの退避」があります。
この記事では、3つの方式を選ぶ判断基準を表にまとめ、論理削除で一意制約が再登録を止めてしまう現象と解決策を PostgreSQL と MySQL で実行して示します。あわせて、「いつまで残し、いつ消すか」を決めるデータ保持期間一覧のテンプレートと、期限切れの行をアーカイブ表へ移して物理削除する SQL を載せます。
確認環境：Ubuntu 24.04 / PostgreSQL 16.15（psql）/ MySQL 8.0.46（mysql クライアント）。確認日 2026-10-05。
この記事の結論
- 削除方式は表ごとに決めます。復元が要る・ほかの表から参照されている → 論理削除、一意制約を素直に保ちたい・件数が多い → 履歴テーブルへ退避、残す理由がない → 物理削除が基本です。
- 削除の印は、deleted_at（削除日時）を基本にし、取消・停止などの状態を区別したいときは状態列にします。
- 論理削除で一意制約が再登録を止める問題は、PostgreSQL は部分インデックス、MySQL は生成列か関数インデックスで解決できます。
- 論理削除は「消さない設計」ではなく「あとで消す設計」です。保持期間一覧を作り、期限が来た行をアーカイブ表へ移して物理削除します。
論理削除・物理削除・履歴テーブル退避の判断基準 早見表
| 観点 | 論理削除 | 履歴テーブルへ退避 | 物理削除 |
|---|---|---|---|
| やること | 行に削除の印を付ける（UPDATE） | 行を別の表へ移して本体から消す（INSERT＋DELETE） | 行を消す（DELETE） |
| 復元 | 印を外すだけ | 退避先から戻す | バックアップから戻す |
| 参照整合性（外部キー） | 親の行が残るので、子はそのまま参照できる | 子を先に移すか、参照されない表に向く | 子がある親は消せない（子を先に消す） |
| 一意制約 | 手当てが要る（後述） | 本体は有効な行だけなので、そのまま使える | そのまま使える |
| 検索条件 | すべての検索に「削除済みを除く」条件が要る | 不要 | 不要 |
| 本体の件数 | 増え続ける | 有効な行だけ | 有効な行だけ |
| 削除済みデータの参照 | 同じ表を検索 | 退避先を検索 | できない |
| 保持期間の管理 | 要る（期限後に退避・物理削除） | 要る（退避先の期限） | 不要 |
選ぶときは、次の条件を上から順に当てはめます。
| 条件 | 選ぶ方式 |
|---|---|
| 残す理由がなく、消えても困らない（セッション、一時ファイル） | 物理削除 |
| 利用者や管理者が画面から「元に戻す」操作をする | 論理削除 |
| ほかの表から参照されていて、過去の記録に名前などを表示し続けたい（会員、備品） | 論理削除（または参照される項目を子の表に写しておく） |
| 一意にしたい列があり、削除後すぐ同じ値で登録し直すことが多い | 履歴テーブルへ退避、または論理削除＋部分インデックス |
| 削除済みの行が有効な行より多くなる見込み | 履歴テーブルへ退避 |
| 一定期間の保持が規程や契約で求められている | 論理削除または退避＋保持期間一覧で期限を管理 |
| 求めがあれば確実に消す必要があるデータ | 物理削除（退避先・バックアップの扱いも決める） |
本記事の内容は、ご自由にお使いください。
ご利用の際は、出典として本ページへのリンクを記載いただけますようお願いします。
（記載例）出典：株式会社RJC「論理削除と物理削除の違いと設計｜削除フラグのデメリットと代替」
削除フラグ・deleted_at・状態列の違い
論理削除の「印」の持ち方は3つあります。
| 持ち方 | 列の例 | 分かること | 向いている場面 | 気をつける点 |
|---|---|---|---|---|
| 削除フラグ | is_deleted（真偽値） | 削除済みかどうか | 既存の表に合わせるとき | いつ消したかが分からず、保持期間の計算に使えない |
| 削除日時 | deleted_at（日時。NULL なら有効） | 削除済みかどうか＋いつ消したか | 新しく作る表の基本 | NULL を使うので、一意制約との組み合わせに注意（後述） |
| 状態列 | status（active / suspended / withdrawn など） | 業務上の状態 | 「取消」「停止」「退会」を区別したいとき | 「どの状態を一覧に出すか」を状態ごとに決める |
削除日時は、削除フラグの情報を含んだうえで保持期間の起点にもなるので、迷ったら deleted_at を選びます。誰が消したかも要るなら deleted_by を足します。
状態列は「削除」という1つの言葉にまとめられていた業務上の出来事を分けて持てます。予約の「取消」と会員の「退会」は、どちらもデータを消すわけではなく状態が変わっただけ、と捉えると、削除フラグを使わずに済む表も多くあります。
削除フラグのデメリットと、起きること
論理削除を選んだときに起きることを、先に一覧にします。
| 起きること | 内容 | 手当て |
|---|---|---|
| 検索条件の書き忘れ | 条件を付け忘れた検索に、削除済みの行が混ざる | 有効な行だけのビューを作り、アプリはビューを読む |
| 一意制約が再登録を止める | 削除済みの行と同じ値で登録できない | 部分インデックス、生成列、関数インデックス |
| 参照整合性が形だけになる | 外部キーは通るが、参照先は削除済み | 表示の仕様を決める（「退会済み」と出す など） |
| 表と索引が大きくなる | 有効な行が少なくても、全行ぶんの大きさになる | 部分インデックス、期限後の退避 |
| いつまでも残る | 消したはずのデータが残り続ける | 保持期間一覧と、定期的な物理削除 |
以下、PostgreSQL と MySQL で順に確かめます。
一意制約が再登録を止める：PostgreSQL での再現と解決
会員のメールアドレスに一意制約を付け、退会（論理削除）したあと同じアドレスで登録し直します。以下は psql に SQL ファイルを流したときの表示で、SQL のすぐ下の行が実行結果です。
-- 1) 素朴な論理削除：一意制約が再登録を止める
CREATE TABLE members (
id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
email text NOT NULL,
name text NOT NULL,
deleted_at timestamptz,
CONSTRAINT members_email_key UNIQUE (email)
);
CREATE TABLE
INSERT INTO members (email, name) VALUES ('taro@example.com', '山田 太郎');
INSERT 0 1
UPDATE members SET deleted_at = '2026-10-01 09:00+09' WHERE email = 'taro@example.com';
UPDATE 1
INSERT INTO members (email, name) VALUES ('taro@example.com', '山田 太郎（再登録）');
psql:pg.sql:12: ERROR: duplicate key value violates unique constraint "members_email_key"
DETAIL: Key (email)=(taro@example.com) already exists.
退会済みの行が残っているので、再登録が一意制約で止まります。
うまくいかない例：一意制約に deleted_at を足す
「email と deleted_at の組で一意にする」方法は、有効な行どうしの重複を止められません。
-- 2) (email, deleted_at) の複合一意：有効な行の重複を止められない
ALTER TABLE members DROP CONSTRAINT members_email_key;
ALTER TABLE
ALTER TABLE members ADD CONSTRAINT members_email_del_key UNIQUE (email, deleted_at);
ALTER TABLE
INSERT INTO members (email, name) VALUES ('taro@example.com', '再登録1');
INSERT 0 1
INSERT INTO members (email, name) VALUES ('taro@example.com', '再登録2');
INSERT 0 1
SELECT id, email, name, deleted_at FROM members ORDER BY id;
id | email | name | deleted_at
----+------------------+-----------+------------------------
1 | taro@example.com | 山田 太郎 | 2026-10-01 09:00:00+09
3 | taro@example.com | 再登録1 |
4 | taro@example.com | 再登録2 |
(3 rows)
有効な行（deleted_at が NULL）が2つ登録できています。一意制約では NULL どうしは「等しくない」と扱われるためです。id が 2 を飛ばして 3 になっているのは、先ほど止まった INSERT で番号が1つ使われたためです。
解決A：部分インデックス（PostgreSQL の基本）
部分インデックスは、条件に合う行だけを対象にした索引です。「有効な行の中でだけ一意」を直接書けます。
-- 3) 解決A：部分インデックス（有効な行だけ一意）
DELETE FROM members WHERE name = '再登録2';
DELETE 1
ALTER TABLE members DROP CONSTRAINT members_email_del_key;
ALTER TABLE
CREATE UNIQUE INDEX members_email_active_key ON members (email) WHERE deleted_at IS NULL;
CREATE INDEX
INSERT INTO members (email, name) VALUES ('taro@example.com', '再登録3');
psql:pg.sql:25: ERROR: duplicate key value violates unique constraint "members_email_active_key"
DETAIL: Key (email)=(taro@example.com) already exists.
UPDATE members SET deleted_at = '2026-10-02 09:00+09' WHERE name = '再登録1';
UPDATE 1
INSERT INTO members (email, name) VALUES ('taro@example.com', '再登録3');
INSERT 0 1
SELECT id, email, name, deleted_at FROM members ORDER BY id;
id | email | name | deleted_at
----+------------------+-----------+------------------------
1 | taro@example.com | 山田 太郎 | 2026-10-01 09:00:00+09
3 | taro@example.com | 再登録1 | 2026-10-02 09:00:00+09
6 | taro@example.com | 再登録3 |
(3 rows)
有効な行があるうちは同じアドレスを登録できず、論理削除したあとは登録できます。削除済みの行は何件あっても構いません。
解決B：生成列
生成列は、ほかの列から自動で計算される列です。有効なら true、削除済みなら NULL になる列を作り、email と組にして一意にします。
-- 4) 解決B：生成列（有効なら true、削除済みなら NULL）
DROP INDEX members_email_active_key;
DROP INDEX
ALTER TABLE members ADD COLUMN is_active boolean
GENERATED ALWAYS AS (CASE WHEN deleted_at IS NULL THEN true END) STORED;
ALTER TABLE
ALTER TABLE members ADD CONSTRAINT members_email_isactive_key UNIQUE (email, is_active);
ALTER TABLE
INSERT INTO members (email, name) VALUES ('taro@example.com', '再登録4');
psql:pg.sql:35: ERROR: duplicate key value violates unique constraint "members_email_isactive_key"
DETAIL: Key (email, is_active)=(taro@example.com, t) already exists.
SELECT id, email, is_active, deleted_at FROM members ORDER BY id;
id | email | is_active | deleted_at
----+------------------+-----------+------------------------
1 | taro@example.com | | 2026-10-01 09:00:00+09
3 | taro@example.com | | 2026-10-02 09:00:00+09
6 | taro@example.com | t |
(3 rows)
削除済みの行は is_active が NULL なので重複とみなされず、有効な行どうしだけが一意になります。PostgreSQL では部分インデックスのほうが簡潔ですが、この方法は MySQL でも同じ考え方で使えます。
解決C：NULLS NOT DISTINCT
PostgreSQL 16 では、一意制約に NULLS NOT DISTINCT を付けて、NULL どうしを「等しい」と扱わせることもできます。
-- 5) 解決C：NULLS NOT DISTINCT
ALTER TABLE members DROP CONSTRAINT members_email_isactive_key;
ALTER TABLE
ALTER TABLE members ADD CONSTRAINT members_email_del_nnd_key UNIQUE NULLS NOT DISTINCT (email, deleted_at);
ALTER TABLE
INSERT INTO members (email, name) VALUES ('taro@example.com', '再登録5');
psql:pg.sql:41: ERROR: duplicate key value violates unique constraint "members_email_del_nnd_key"
DETAIL: Key (email, deleted_at)=(taro@example.com, null) already exists.
有効な行の重複が止まりました。削除済みの行は deleted_at の値が違えば共存できます。同じ時刻に削除された同じアドレスの行は重複になる点が、部分インデックスとの違いです。
一意制約が再登録を止める：MySQL での再現と解決
MySQL 8.0 でも同じ現象が起きます。
CREATE TABLE members (
id BIGINT AUTO_INCREMENT PRIMARY KEY,
email VARCHAR(255) NOT NULL,
name VARCHAR(100) NOT NULL,
deleted_at DATETIME NULL,
UNIQUE KEY members_email_key (email)
);
INSERT INTO members (email, name) VALUES ('taro@example.com', '山田 太郎');
UPDATE members SET deleted_at = '2026-10-01 09:00:00' WHERE email = 'taro@example.com';
INSERT INTO members (email, name) VALUES ('taro@example.com', '山田 太郎（再登録）');
ERROR 1062 (23000) at line 11: Duplicate entry 'taro@example.com' for key 'members.members_email_key'
email と deleted_at の複合一意にしても、PostgreSQL と同じく有効な行が重複します。
ALTER TABLE members DROP INDEX members_email_key, ADD UNIQUE KEY members_email_del_key (email, deleted_at);
INSERT INTO members (email, name) VALUES ('taro@example.com', '再登録1');
INSERT INTO members (email, name) VALUES ('taro@example.com', '再登録2');
SELECT id, email, name, deleted_at FROM members ORDER BY id;
+----+------------------+---------------+---------------------+
| id | email | name | deleted_at |
+----+------------------+---------------+---------------------+
| 1 | taro@example.com | 山田 太郎 | 2026-10-01 09:00:00 |
| 3 | taro@example.com | 再登録1 | NULL |
| 4 | taro@example.com | 再登録2 | NULL |
+----+------------------+---------------+---------------------+
MySQL には部分インデックスの構文がありません。PostgreSQL と同じ書き方をすると構文エラーになります。
DELETE FROM members WHERE name = '再登録2';
CREATE UNIQUE INDEX members_email_active_key ON members (email) WHERE deleted_at IS NULL;
ERROR 1064 (42000) at line 21: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'WHERE deleted_at IS NULL' at line 1
解決A：生成列
ALTER TABLE members DROP INDEX members_email_del_key;
ALTER TABLE members
ADD COLUMN is_active TINYINT GENERATED ALWAYS AS (IF(deleted_at IS NULL, 1, NULL)) VIRTUAL,
ADD UNIQUE KEY members_email_isactive_key (email, is_active);
INSERT INTO members (email, name) VALUES ('taro@example.com', '再登録3');
UPDATE members SET deleted_at = '2026-10-02 09:00:00' WHERE name = '再登録1';
INSERT INTO members (email, name) VALUES ('taro@example.com', '再登録3');
SELECT id, email, name, is_active, deleted_at FROM members ORDER BY id;
実行結果です（1回目の INSERT は有効な行があるので止まり、論理削除したあとの2回目は通ります）。
ERROR 1062 (23000) at line 28: Duplicate entry 'taro@example.com-1' for key 'members.members_email_isactive_key'
+----+------------------+---------------+-----------+---------------------+
| id | email | name | is_active | deleted_at |
+----+------------------+---------------+-----------+---------------------+
| 1 | taro@example.com | 山田 太郎 | NULL | 2026-10-01 09:00:00 |
| 3 | taro@example.com | 再登録1 | NULL | 2026-10-02 09:00:00 |
| 6 | taro@example.com | 再登録3 | 1 | NULL |
+----+------------------+---------------+-----------+---------------------+
解決B：関数インデックス
MySQL 8.0.13 以降は、式に対して索引を作れます（式は二重のかっこで囲みます）。有効な行のときだけ email を返し、削除済みなら NULL を返す式を一意にします。列を増やさずに済みます。
ALTER TABLE members DROP INDEX members_email_isactive_key, DROP COLUMN is_active;
CREATE UNIQUE INDEX members_email_active_key
ON members ((CASE WHEN deleted_at IS NULL THEN email END));
INSERT INTO members (email, name) VALUES ('taro@example.com', '再登録4');
UPDATE members SET deleted_at = '2026-10-03 09:00:00' WHERE name = '再登録3';
INSERT INTO members (email, name) VALUES ('taro@example.com', '再登録4');
SELECT id, email, name, deleted_at FROM members ORDER BY id;
ERROR 1062 (23000) at line 37: Duplicate entry 'taro@example.com' for key 'members.members_email_active_key'
+----+------------------+---------------+---------------------+
| id | email | name | deleted_at |
+----+------------------+---------------+---------------------+
| 1 | taro@example.com | 山田 太郎 | 2026-10-01 09:00:00 |
| 3 | taro@example.com | 再登録1 | 2026-10-02 09:00:00 |
| 6 | taro@example.com | 再登録3 | 2026-10-03 09:00:00 |
| 8 | taro@example.com | 再登録4 | NULL |
+----+------------------+---------------+---------------------+
DB 別の解決策まとめ
| 方法 | PostgreSQL 16 | MySQL 8.0 | 特徴 |
|---|---|---|---|
| 部分インデックス（WHERE 付きの一意索引） | 使える | 構文なし（ERROR 1064） | 意図がそのまま書ける。索引も小さい |
| 生成列＋複合一意 | 使える | 使える | 両方の DB で同じ考え方が通る |
| 関数インデックス（式の一意索引） | 今回は対象外（部分インデックスで足りるため） | 使える（8.0.13 以降） | 列を増やさない |
| NULLS NOT DISTINCT | 使える | 構文エラー（ERROR 1064） | 既存の複合一意を活かせる |
| (一意列, deleted_at) の複合一意だけ | 有効な行が重複する | 有効な行が重複する | 単独では目的を果たさない |
参照整合性：外部キーと論理削除
予約（子）が会員（親）を参照しているとき、親の物理削除は外部キーで止まります。
-- 6) 参照整合性：子の行がある親は物理削除できない
CREATE TABLE reservations (
id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
member_id bigint NOT NULL REFERENCES members (id),
item text NOT NULL,
use_date date NOT NULL
);
CREATE TABLE
INSERT INTO reservations (member_id, item, use_date) VALUES (1, 'プロジェクター', '2026-09-20');
INSERT 0 1
DELETE FROM members WHERE id = 1;
psql:pg.sql:51: ERROR: update or delete on table "members" violates foreign key constraint "reservations_member_id_fkey" on table "reservations"
DETAIL: Key (id)=(1) is still referenced from table "reservations".
-- 論理削除済みの親は残るので、子は参照し続ける
SELECT r.id, r.item, m.name, m.deleted_at IS NOT NULL AS member_deleted
FROM reservations r JOIN members m ON m.id = r.member_id;
id | item | name | member_deleted
----+----------------+-----------+----------------
1 | プロジェクター | 山田 太郎 | t
(1 row)
MySQL でも同じく止まります。
ERROR 1451 (23000) at line 50: Cannot delete or update a parent row: a foreign key constraint fails (`appdb`.`reservations`, CONSTRAINT `reservations_ibfk_1` FOREIGN KEY (`member_id`) REFERENCES `members` (`id`))
論理削除なら親の行は残るので、過去の予約に会員名を表示し続けられます。一方で、外部キーは「行があるか」だけを見るので、参照先が削除済みかどうかは保証しません。上の結果でも、予約が削除済みの会員（member_deleted が t）を指しています。設計書には、次のどれにするかを書いておきます。
| 方針 | 内容 |
|---|---|
| そのまま表示 | 過去の記録として名前を出す。一覧に「退会済み」と添える |
| 新規の参照だけ止める | 削除済みの親を選べないよう、登録時にアプリで確かめる |
| 子に写しておく | 予約の行に、その時点の会員名を持たせる。親を消しても記録が読める |
有効な行だけのビュー
検索条件の書き忘れは、有効な行だけを返すビューで減らせます。
-- 7) 有効な行だけのビュー
CREATE VIEW active_members AS SELECT id, email, name FROM members WHERE deleted_at IS NULL;
CREATE VIEW
SELECT * FROM active_members;
id | email | name
----+------------------+---------
6 | taro@example.com | 再登録3
(1 row)
件数が増えたときの索引の大きさ
20万行のうち9割が削除済みという表で、全行の索引と、有効な行だけの部分インデックスの大きさを比べました。
-- 索引の大きさ：全行の索引と、有効な行だけの部分インデックス
CREATE TABLE big (id bigint PRIMARY KEY, email text NOT NULL, deleted_at timestamptz);
CREATE TABLE
INSERT INTO big SELECT n, 'u' || n || '@example.com', CASE WHEN n % 10 <> 0 THEN now() END
FROM generate_series(1, 200000) AS n;
INSERT 0 200000
CREATE INDEX big_email_all ON big (email);
CREATE INDEX
CREATE UNIQUE INDEX big_email_active ON big (email) WHERE deleted_at IS NULL;
CREATE INDEX
SELECT count(*) AS total, count(*) FILTER (WHERE deleted_at IS NULL) AS active FROM big;
total | active
--------+--------
200000 | 20000
(1 row)
SELECT pg_size_pretty(pg_relation_size('big_email_all')) AS all_rows,
pg_size_pretty(pg_relation_size('big_email_active')) AS active_only;
all_rows | active_only
----------+-------------
7960 kB | 808 kB
(1 row)
ANALYZE big;
ANALYZE
EXPLAIN (COSTS OFF) SELECT id FROM big WHERE email = 'u10@example.com' AND deleted_at IS NULL;
QUERY PLAN
-------------------------------------------------
Index Scan using big_email_active on big
Index Cond: (email = 'u10@example.com'::text)
(2 rows)
有効な2万行だけの部分インデックスは 808 kB で、全行の索引 7960 kB の約10分の1でした。検索にも部分インデックスが使われています。削除済みの行が多い表ほど、部分インデックスの効果が出ます。ただし、表そのものの大きさは変わらないので、根本の対処は次の「期限が来たら移して消す」です。
履歴テーブルへ退避する方式
削除と同時に、行を別の表へ移す方式です。本体の表には有効な行だけが残るので、一意制約も検索条件もそのまま使えます。PostgreSQL では、DELETE … RETURNING の結果を INSERT に渡すと1文で書けます。
INSERT 0 2
-- 削除と同時に履歴テーブルへ移す
WITH moved AS (
DELETE FROM items WHERE code = 'PJ-01' RETURNING id, code, name
)
INSERT INTO items_deleted (id, code, name, deleted_at, deleted_by)
SELECT id, code, name, timestamptz '2026-10-05 10:00+09', 'sato' FROM moved;
INSERT 0 1
-- 本体は有効な行だけ。同じコードをすぐ登録できる
INSERT INTO items (code, name) VALUES ('PJ-01', 'プロジェクター（新）');
INSERT 0 1
SELECT * FROM items ORDER BY id;
id | code | name
----+-------+----------------------
2 | WB-01 | ホワイトボード
3 | PJ-01 | プロジェクター（新）
(2 rows)
SELECT * FROM items_deleted;
id | code | name | deleted_at | deleted_by
----+-------+----------------+------------------------+------------
1 | PJ-01 | プロジェクター | 2026-10-05 10:00:00+09 | sato
(1 row)
-- 復元するときは逆向きに移す（コードが使用中なら一意制約で止まる）
WITH back AS (
DELETE FROM items_deleted WHERE id = 1 RETURNING id, code, name
)
INSERT INTO items (id, code, name) OVERRIDING SYSTEM VALUE
SELECT id, code, name FROM back;
psql:pg_move.sql:33: ERROR: duplicate key value violates unique constraint "items_code_key"
DETAIL: Key (code)=(PJ-01) already exists.
SELECT count(*) AS still_in_deleted FROM items_deleted;
still_in_deleted
------------------
最後の復元は、同じコードの備品がすでに登録されていたので一意制約で止まり、退避先の行も消えずに残りました（1文の中の DELETE と INSERT はまとめて取り消されます）。復元の仕様には「同じ値がすでに使われていたらどうするか」を入れておく必要があることが分かります。
データ保持期間一覧テンプレート
論理削除や退避を選んだ表は、「いつまで残すか」を一覧にします。1行が1種類のデータです。期間の数字は記入例で、実際の期間は適用される法令・契約・社内規程を確認して決めます。
| データ | テーブル | 削除方式 | 本体に残す期間 | 退避先 | 退避先での保持期間 | 物理削除の契機 | 保持の根拠 | 担当 |
|---|---|---|---|---|---|---|---|---|
| 会員 | members | 論理削除（deleted_at） | 退会から180日 | members_archive |
退避から5年 | 月次バッチ | 利用規約、社内規程 | 会員管理の担当 |
| 予約申請 | reservations | 論理削除（状態列：取消） | 利用日から2年 | reservations_archive |
退避から5年 | 月次バッチ | 社内規程（備品の利用記録） | 総務の担当 |
| 問い合わせ | inquiries | 論理削除（deleted_at） | 削除から180日 | inquiries_archive |
退避から5年 | 月次バッチ | 社内規程 | サポートの担当 |
| 備品マスタ | items | 履歴テーブルへ退避 | 0日（削除と同時に退避） | items_deleted |
退避から10年 | 年次バッチ | 社内規程（資産の記録） | 総務の担当 |
| 操作ログ | audit_logs |
物理削除のみ（削除操作なし） | 1年 | ファイルへ書き出して保管 | 3年 | 月次バッチ | 社内規程（監査） | 運用の担当 |
| メール送信依頼 | mail_outbox |
物理削除 | 送信から90日 | なし | ― | 日次バッチ | 調査に使う期間 | 運用の担当 |
| ログインセッション | sessions | 物理削除 | 期限切れから1日 | なし | ― | 日次バッチ | 保持の必要なし | 運用の担当 |
| 一時アップロード | temp_files |
物理削除 | 作成から1日 | なし | ― | 日次バッチ | 保持の必要なし | 運用の担当 |
表計算ソフト用のタブ区切りです。
データ テーブル 削除方式 本体に残す期間 退避先 退避先での保持期間 物理削除の契機 保持の根拠 担当
会員 members 論理削除（deleted_at） 退会から180日 members_archive 退避から5年 月次バッチ 利用規約、社内規程 会員管理の担当
予約申請 reservations 論理削除（状態列：取消） 利用日から2年 reservations_archive 退避から5年 月次バッチ 社内規程（備品の利用記録） 総務の担当
問い合わせ inquiries 論理削除（deleted_at） 削除から180日 inquiries_archive 退避から5年 月次バッチ 社内規程 サポートの担当
備品マスタ items 履歴テーブルへ退避 0日（削除と同時に退避） items_deleted 退避から10年 年次バッチ 社内規程（資産の記録） 総務の担当
操作ログ audit_logs 物理削除のみ（削除操作なし） 1年 ファイルへ書き出して保管 3年 月次バッチ 社内規程（監査） 運用の担当
メール送信依頼 mail_outbox 物理削除 送信から90日 なし ― 日次バッチ 調査に使う期間 運用の担当
ログインセッション sessions 物理削除 期限切れから1日 なし ― 日次バッチ 保持の必要なし 運用の担当
一時アップロード temp_files 物理削除 作成から1日 なし ― 日次バッチ 保持の必要なし 運用の担当
| 列 | 書くこと |
|---|---|
| 削除方式 | 論理削除（印の持ち方）、履歴テーブルへ退避、物理削除 |
| 本体に残す期間 | 起点（削除日、退会日、利用日、作成日）と長さ |
| 退避先 | アーカイブ表の名前、またはファイル。なければ「なし」 |
| 退避先での保持期間 | 起点と長さ。過ぎたら物理削除 |
| 物理削除の契機 | 日次・月次・年次のバッチ、または手動 |
| 保持の根拠 | 何に基づく期間か。根拠が書けない行は期間を見直す |
| 担当 | 期間の変更を判断する人 |
バックアップにも同じデータが残ります。「確実に消す」必要があるデータは、バックアップの保持期間も一覧の備考に書いておくと、問い合わせに答えやすくなります。
期限切れの行をアーカイブ表へ移して物理削除する SQL
問い合わせ表を例に、「論理削除から180日を過ぎた行をアーカイブ表へ移し、本体から物理削除する」処理を実行します。基準日は 2026-10-05 に固定しています。
PostgreSQL
表の定義と、確認用のデータ（600件、うち200件が論理削除済み）です。
CREATE TABLE inquiries (
id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
title text NOT NULL,
created_at timestamptz NOT NULL,
deleted_at timestamptz
);
CREATE TABLE
CREATE TABLE inquiries_archive (
id bigint PRIMARY KEY,
title text NOT NULL,
created_at timestamptz NOT NULL,
deleted_at timestamptz,
archived_at timestamptz NOT NULL
);
CREATE TABLE
-- 2025-01-01 から1日1件、600件。3件に1件を作成30日後に論理削除
INSERT INTO inquiries (title, created_at, deleted_at)
SELECT '問い合わせ' || n,
timestamptz '2025-01-01 00:00+09' + (n || ' days')::interval,
CASE WHEN n % 3 = 0 THEN timestamptz '2025-01-01 00:00+09' + ((n + 30) || ' days')::interval END
FROM generate_series(0, 599) AS n;
INSERT 0 600
SELECT count(*) AS total, count(deleted_at) AS soft_deleted FROM inquiries;
total | soft_deleted
-------+--------------
600 | 200
(1 row)
移動と物理削除は1文で書けます。
-- 論理削除から180日を過ぎた行を、アーカイブ表へ移して物理削除（1文で完結）
BEGIN;
BEGIN
WITH moved AS (
DELETE FROM inquiries
WHERE deleted_at < timestamptz '2026-10-05 00:00+09' - interval '180 days'
RETURNING id, title, created_at, deleted_at
)
INSERT INTO inquiries_archive (id, title, created_at, deleted_at, archived_at)
SELECT id, title, created_at, deleted_at, timestamptz '2026-10-05 00:00+09' FROM moved;
INSERT 0 144
COMMIT;
COMMIT
SELECT (SELECT count(*) FROM inquiries) AS remaining,
(SELECT count(*) FROM inquiries_archive) AS archived,
(SELECT max(deleted_at)::date FROM inquiries_archive) AS newest_archived;
remaining | archived | newest_archived
-----------+----------+-----------------
456 | 144 | 2026-04-05
(1 row)
144件がアーカイブ表へ移り、本体は456件になりました。アーカイブ表の期限切れと、件数を区切った削除は次のとおりです。
-- アーカイブ表の保持期間（例：5年）を過ぎた行を物理削除
DELETE FROM inquiries_archive
WHERE archived_at < timestamptz '2026-10-05 00:00+09' - interval '5 years';
DELETE 0
-- 件数が多いときは、1回の件数を区切って繰り返す
WITH target AS (
SELECT id FROM inquiries
WHERE deleted_at < timestamptz '2026-10-05 00:00+09' - interval '90 days'
ORDER BY id LIMIT 10
)
DELETE FROM inquiries i USING target t WHERE i.id = t.id;
DELETE 10
バッチに組み込むときは、基準日の部分を now() に置き換えます。
MySQL
MySQL 8.0.46 では DELETE … RETURNING が構文エラー（ERROR 1064）になるので、同じ条件の INSERT … SELECT と DELETE を1つのトランザクションにまとめます。
CREATE TABLE inquiries (
id BIGINT AUTO_INCREMENT PRIMARY KEY,
title VARCHAR(100) NOT NULL,
created_at DATETIME NOT NULL,
deleted_at DATETIME NULL,
KEY inquiries_deleted_at (deleted_at)
);
CREATE TABLE inquiries_archive (
id BIGINT PRIMARY KEY,
title VARCHAR(100) NOT NULL,
created_at DATETIME NOT NULL,
deleted_at DATETIME NULL,
archived_at DATETIME NOT NULL
);
INSERT INTO inquiries (title, created_at, deleted_at)
WITH RECURSIVE seq (n) AS (SELECT 0 UNION ALL SELECT n + 1 FROM seq WHERE n < 599)
SELECT CONCAT('問い合わせ', n),
'2025-01-01' + INTERVAL n DAY,
IF(n % 3 = 0, '2025-01-01' + INTERVAL (n + 30) DAY, NULL)
FROM seq;
SELECT COUNT(*) AS total, COUNT(deleted_at) AS soft_deleted FROM inquiries;
SET @today = '2026-10-05 00:00:00';
START TRANSACTION;
INSERT INTO inquiries_archive (id, title, created_at, deleted_at, archived_at)
SELECT id, title, created_at, deleted_at, @today
FROM inquiries
WHERE deleted_at < @today - INTERVAL 180 DAY
FOR UPDATE;
DELETE FROM inquiries WHERE deleted_at < @today - INTERVAL 180 DAY;
SELECT ROW_COUNT() AS deleted_rows;
COMMIT;
SELECT (SELECT COUNT(*) FROM inquiries) AS remaining,
(SELECT COUNT(*) FROM inquiries_archive) AS archived,
(SELECT DATE(MAX(deleted_at)) FROM inquiries_archive) AS newest_archived;
-- 件数が多いときは LIMIT で区切って繰り返す
実行結果です。
Query OK, 144 rows affected
Records: 144 Duplicates: 0 Warnings: 0
Query OK, 144 rows affected
+--------------+
| deleted_rows |
+--------------+
| 144 |
+--------------+
+-----------+----------+-----------------+
| remaining | archived | newest_archived |
+-----------+----------+-----------------+
| 456 | 144 | 2026-04-05 |
+-----------+----------+-----------------+
件数が多いときは、DELETE に ORDER BY と LIMIT を付けて区切ります。
DELETE FROM inquiries WHERE deleted_at < @today - INTERVAL 90 DAY ORDER BY id LIMIT 10;
SELECT ROW_COUNT() AS deleted_rows;
+--------------+
| deleted_rows |
+--------------+
| 10 |
+--------------+
バッチにするときの確認項目
| 番号 | 確認項目 |
|---|---|
| 1 | 移動と削除が同じトランザクション（または1文）になっている |
| 2 | 移した件数と消した件数が一致することを記録している |
| 3 | 条件に使う列（deleted_at）に索引がある |
| 4 | 子の表がある場合、子から先に移す順番になっている |
| 5 | 途中で止まっても、再実行すれば続きから進む（同じ行を2回移さない） |
| 6 | 1回の件数を区切り、長いロックを避けている |
| 7 | 基準日とタイムゾーンを決めている |
確かめて分かったこと
- (email, deleted_at) の複合一意は、PostgreSQL でも MySQL でも有効な行の重複を止めませんでした。一意制約を「足せば済む」ように見えますが、NULL の扱いで目的と逆の結果になります。
- 部分インデックスの書き方は MySQL では ERROR 1064（構文エラー）になりました。DB を移すときに、そのままでは持ち込めない定義です。
- MySQL の生成列のエラー文は、重複した値が
taro@example.com-1と表示されました。email と生成列の値がつながった形で、アプリでエラー文を解釈している場合は見え方が変わります。 - 一意制約で止まった INSERT でも、連番は1つ進みました。PostgreSQL・MySQL とも id が 1 の次は 3 になっています。番号の欠けは不具合ではありません。
- 9割が削除済みの20万行では、部分インデックスは全行の索引の約10分の1（808 kB と 7960 kB）でした。
- 退避先から復元する SQL は、同じ値が再登録されていると一意制約で止まり、退避先の行はそのまま残りました。
- PostgreSQL の実行結果の日付は、セッションのタイムゾーンで変わりました。タイムゾーンを設定せずに流した最初の実行では、アーカイブした行の最新の削除日が 2026-04-04 と表示され、Asia/Tokyo を設定すると MySQL と同じ 2026-04-05 になりました。保持期間の境目を日付で確かめるときは、タイムゾーンを揃えます。
よくある質問
Q. 論理削除と物理削除の違いは何ですか？ A. 論理削除は行を残して削除の印を付ける方式、物理削除は行そのものを消す方式です。論理削除は UPDATE、物理削除は DELETE で行い、復元のしやすさ、一意制約や検索条件への影響、データ量が変わります。
Q. 削除フラグのデメリットは何ですか？ A. 検索条件の書き忘れ、一意制約が再登録を止めること、表と索引が大きくなること、いつ消したかが分からないことの4つが主なものです。削除日時（deleted_at）にすれば時期が分かり、ビューと部分インデックスでほかの点も手当てできます。
Q. 論理削除で同じメールアドレスを再登録できないときは、どうしますか？ A. 一意制約を「有効な行だけ」に絞ります。PostgreSQL は WHERE deleted_at IS NULL を付けた一意の部分インデックス、MySQL は生成列か関数インデックスを使います。
Q. 論理削除したデータは、いつ消せばよいですか？ A. データの種類ごとに保持期間を決め、期限が来たらアーカイブ表へ移すか物理削除します。期間は法令・契約・社内規程を確認して決め、保持期間一覧に根拠と一緒に書きます。
Q. 履歴テーブルと論理削除は、どちらを選べばよいですか？ A. 削除済みの行を日常的に画面で扱う（元に戻す、一覧に出す）なら論理削除、ほとんど見ないなら履歴テーブルへの退避が向いています。退避は本体の表を有効な行だけに保てるので、一意制約と検索が単純になります。
まとめ
- 論理削除は印を付けて残す方式、物理削除は行を消す方式で、その中間に履歴テーブルへの退避があります。
- 方式は復元の要否・参照整合性・一意制約・件数・保持義務で、表ごとに選びます。
- 印の持ち方は deleted_at を基本に、業務上の状態を区別するなら状態列にします。
- (一意列, deleted_at) の複合一意は有効な行の重複を止めないので、PostgreSQL は部分インデックス、MySQL は生成列か関数インデックスを使います。
- 外部キーは参照先が削除済みかどうかを保証しないので、表示と新規参照の方針を設計書に書きます。
- 保持期間一覧を作り、期限切れの行をアーカイブ表へ移して物理削除するところまでが削除の設計です。
参考・出典
- PostgreSQL 16 Documentation「11.8. Partial Indexes」（https://www.postgresql.org/docs/16/indexes-partial.html）
- MySQL 8.0 Reference Manual「CREATE INDEX Statement」（https://dev.mysql.com/doc/refman/8.0/en/create-index.html）
いずれも 2026-10-05 に内容を確認しました。
本記事の内容は、ご自由にお使いください。
ご利用の際は、出典として本ページへのリンクを記載いただけますようお願いします。
（記載例）出典：株式会社RJC「論理削除と物理削除の違いと設計｜削除フラグのデメリットと代替」
株式会社RJC ― SI事業・SES事業・AI駆動開発。RJCは一緒に成長を楽しめる会社です。
WE ARE HIRING
RJCで一緒に開発しながら、
成長を楽しみませんか？
RJCは、Web・モバイル・AIを活用した開発プロジェクトで、テックリードやPM・PMOも活躍するシステム開発会社です。会社を知る、待遇を確かめる、話を聞いてみる。気になるところ見てみてください！
- 127日年間休日
- 12時間平均残業時間
- 毎日ガチャ遊びココロも大切にする福利厚生。アマギフなどの賞品ラインナップ！
ほかにも、チケットレストラン、書籍読み放題、2年ごとの慰労報奨（休暇 or 金一封）、11期連続の黒字決算。
