行を削除する - DELETE

行を削除する。DELETE USING (PostgreSQL)、外部キー制約、TRUNCATE との違い

概念図

構文

sql

DELETE FROM table WHERE 条件;

サンプル

期限切れセッションを削除する

sql

DELETE FROM sessions
WHERE expires_at < CURRENT_TIMESTAMP;

DELETE の基本

DELETE は指定した行をテーブルから取り除く DML です。WHERE で対象を絞ります。WHERE を省略すると全行削除になるため、UPDATE と同じく事故に注意します。

DELETE はトランザクション内で ROLLBACK 可能、外部キー制約（ON DELETE RESTRICT / CASCADE / SET NULL）を尊重し、トリガが発火します。

sql

-- 1 行削除
DELETE FROM users WHERE id = 42;

-- 条件一致を全削除
DELETE FROM logs WHERE created_at < CURRENT_DATE - INTERVAL '90 days';

PostgreSQL の DELETE USING

他テーブルを条件に削除したい場合、PostgreSQL は DELETE ... USING を使います。UPDATE の FROM と同じ感覚で、JOIN 相当の削除が書けます。

MySQL は DELETE t1 FROM t1 JOIN t2 ...、SQL Server は DELETE t1 FROM t1 JOIN t2 ...、SQLite は DELETE ... WHERE EXISTS (...) などで表現します。

sql

-- PostgreSQL: 退会ユーザーの注文を削除
DELETE FROM orders o
USING users u
WHERE o.user_id = u.id
  AND u.status = 'deleted';

-- MySQL / SQL Server 相当
DELETE o
FROM orders o
JOIN users u ON u.id = o.user_id
WHERE u.status = 'deleted';

外部キー制約との関係

ON DELETE RESTRICT / NO ACTION: 子レコードがあると親削除はエラー
ON DELETE CASCADE: 親を消すと子も自動削除。便利だが、意図しない連鎖削除に注意
ON DELETE SET NULL: 子の外部キー列を NULL に。論理削除の代替として使える

CASCADE は強力ですが、「ユーザーを消したら注文履歴も消滅」のような業務上望ましくない削除も起こります。監査・会計データには CASCADE を付けないのが鉄則です。

DELETE と TRUNCATE の違い

速度: 全件削除なら TRUNCATE が圧倒的に速い（行ごとにログを書かない）
WHERE: DELETE は条件指定可、TRUNCATE は全件のみ
トリガ: DELETE は発火、TRUNCATE は（多くの RDBMS で）発火しない
トランザクション: DELETE は ROLLBACK 可。TRUNCATE は PostgreSQL / SQL Server ではトランザクション内で使えるが MySQL では暗黙コミットされる

詳細は TRUNCATE ページを参照。

落とし穴: WHERE 忘れと巨大 DELETE

WHERE 忘れ: UPDATE と同じく全件消失の事故。明示トランザクション + 件数確認を習慣化
巨大 DELETE: 数千万行を 1 DELETE で消すとロング・ランニング・トランザクションになりロック・WAL 膨張の原因。バッチ分割（DELETE ... WHERE id IN (SELECT id FROM ... LIMIT 1000) をループ）が定石
レプリカ遅延: レプリケーション環境では巨大 DELETE がレプリカ遅延の主原因になる

sql

-- バッチ削除の例 (PostgreSQL)
DELETE FROM logs
WHERE id IN (
  SELECT id FROM logs
  WHERE created_at < CURRENT_DATE - INTERVAL '1 year'
  LIMIT 10000
);
-- これをループで繰り返す