［MariaDB］論理削除（ソフトデリート）

公開日：2026-01-15
更新日：2026-01-15

1. 概要

論理削除（ソフトデリート）についてです。

2. メリット・デメリット

メリット

・誤って削除した際に、簡単に復元できる。
→ 但し、UPDATE の誤操作は復元できないため、論理削除は誤操作対策としては不完全です。

・削除の履歴が残せる。
→ 同じカラムを持つ履歴テーブルに元のデータを保存することで、論理削除にしなくても対応可能。

デメリット

・データ量が増えてディスク容量を圧迫する。
→ 定期的に論理削除されたデータを削除する。

・データ量が増えて SQL が遅くなる。

・インデックスの効率が下がる可能性がある。

・バックアップサイズが増えて、バックアップ・リストアの時間も増える。

・SQL で毎回 deleted_at を指定する必要がある。
→ フレームワークを使用することで対応可能。

・外部キー制約が効かなくなる。親レコードだけが論理削除されてもエラーにならない。

・論理削除されたデータが存在するため、ユニーク制約を工夫する必要がある。

・個人情報保護のための完全な削除にならない。
→ 別途、論理削除されたデータを物理削除するか、論理削除をあきらめる必要がある。

3. 論理削除を採用した時のユニーク制約

ユーザの email を重複不可にする場合、email をユニーク制約にしますが、
論理削除を採用している場合、deleted_at があるため、email だけではユニーク制約が効きません。
そこで、email と deleted_at を併せて複合ユニーク制約にしても、複合ユニーク制約が効きません。
これは、deleted_at が NULL だった場合、NULL は「空」ではなく、「値が存在しない、不明」と言う意味のため、NULL と NULL は異なるものとして判定されるためです。
そのため NULL かどうかの判定も、「= NULL」ではなく、「IS NULL」を使って判定する必要があります。

コマンド

CREATE TABLE users (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(255) NOT NULL,
    email VARCHAR(255) NOT NULL,
    
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    deleted_at TIMESTAMP NULL DEFAULT NULL,
    
    UNIQUE KEY unique_email_deleted_at (email, deleted_at)
);

# email が同じ、deleted_at も NULL で同じなのに、ユニーク制約が効かずに追加できてしまう
INSERT INTO users (name, email) VALUES ('taro', 'taro@example.com');
INSERT INTO users (name, email) VALUES ('jiro', 'taro@example.com');

# deleted_at に NULL 以外の値が入ってる時は、email と deleted_at のユニーク制約が効く。
INSERT INTO users (name, email, deleted_at) VALUES ('saburo', 'taro@example.com', '2026-01-15');
INSERT INTO users (name, email, deleted_at) VALUES ('shiro',  'taro@example.com', '2026-01-15');
# ERROR 1062 (23000): Duplicate entry 'taro@example.com-2026-01-15 00:00:00' for key 'unique_email_deleted_at'

生成カラムを使って論理削除の場合でもユニーク制約が効くようにする。

コマンド

CREATE TABLE users (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(255) NOT NULL,
    email VARCHAR(255) NOT NULL,
    
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    deleted_at TIMESTAMP NULL DEFAULT NULL,
    
    # 生成カラム
    active_email VARCHAR(255)
        GENERATED ALWAYS AS (
        IF(deleted_at IS NULL, email, NULL)  #  
    ) VIRTUAL,
    
    # 生成カラムにユニーク制約を付ける
    UNIQUE KEY uk_active_email (active_email)
);

INSERT INTO users (name, email) VALUES ('taro', 'taro@example.com');
INSERT INTO users (name, email) VALUES ('jiro', 'taro@example.com'); # エラー
INSERT INTO users (name, email, deleted_at) VALUES ('taro', 'taro@example.com', now()); # deleted_at が異なるため OK
INSERT INTO users (name, email, deleted_at) VALUES ('taro', 'taro@example.com', now()); # deleted_at が異なるため OK

＜前の記事

インデックス

次の記事＞

楽観ロック・悲観ロック

［MariaDB］論理削除（ソフトデリート）

1. 概要

2. メリット・デメリット

メリット

デメリット

3. 論理削除を採用した時のユニーク制約

新着一覧

アーカイブ

以前のカテゴリー一覧