DELETEは行を消すだけ、それ以上は何もしない
DELETEはテーブルから行を取り除くための命令です。テーブル自体を消したり、スキーマを変えたり、他のテーブルに影響を与えたりはしません(カスケード設定をしていない限り)。構文もシンプルです:
DELETE FROM users WHERE id = 2; は条件にマッチする行を探して削除します。残りの2行はそのまま残ります。テーブル自体は消えないので、引き続きINSERTもできます。
イメージとしては、DELETEは「マッチした行を返す代わりに捨てるSELECT」だと考えると分かりやすいです。
WHERE句がすべてを握っている
実用的なDELETE文は、WHERE句の出来で成否が決まります。書き方が正しければ意図した行だけが消えますが、間違えると想定より多くの行が、最悪の場合はテーブル全件が吹き飛びます。
未公開かつ閲覧数ゼロの下書きは2件とも消え、公開済みの行は条件にマッチしないので残ります。WHERE で使える式なら何でもOKです。IN、LIKE、BETWEEN、サブクエリ、AND / OR の組み合わせなど自由に書けます。
身につけておきたい習慣として、DELETE を実行する前に、同じ WHERE 句を SELECT で試しておきましょう。
-- 削除対象を事前確認:
SELECT * FROM posts WHERE published = 0 AND views = 0;
-- 行に問題なければ、削除を実行:
DELETE FROM posts WHERE published = 0 AND views = 0;
あの2ステップを踏むだけで、バックアップツール総出でも救えなかったほどのデータベースが守られてきました。
WHERE を省略した DELETE は全件削除になる
WHERE を書かずに DELETE を実行すると、テーブルの中身がまるごと消えます:
テーブル自体は残ったまま、中身だけが空になります。SQLite には TRUNCATE 文がないので、その代わりに DELETE FROM table; を使います。SQLite は内部的に「truncate optimization（切り詰め最適化）」を効かせて、行を1件ずつ消すのではなくページ単位で一気に破棄してくれます。速いですが、それでもトランザクションの一部なのでロールバック可能です。
主キーに AUTOINCREMENT を指定している場合、カウンタは自動ではリセットされません。id を 1 から振り直したいときは、シーケンス管理用の行も合わせて消しておきます。
DELETE FROM log;
DELETE FROM sqlite_sequence WHERE name = 'log';
AUTOINCREMENT を付けない素の INTEGER PRIMARY KEY であれば、SQLite はそもそも ID を自由に再利用するので、この処理は不要です。
複数行をまとめて削除する
特定の複数行を消したいときは、IN を使うのがいちばんスッキリ書けます。
サブクエリを使って削除対象を決めることもできます。別テーブルとのJOINや条件で消したい行を絞り込みたいときに便利です。
SQLite には MySQL のような DELETE ... JOIN 構文はありませんが、WHERE 句にサブクエリを書けば同じことができます。
RETURNING で削除した行を確認する
RETURNING を付けると、削除された行を SELECT のように結果セットとして取得できます。
削除された行の id と email がそのまま返ってきます。これが便利なのは次のようなケースです。
- 何が削除されたかをログに正確に残したいとき
- アンドゥ機能を作るとき(返ってきた行をどこかに退避しておけばOK)
- 想定どおりの行が削除されたかを、1回のやり取りで確認したいとき
RETURNING は INSERT・UPDATE・DELETE のいずれでも使えます。詳しい使い方は専用のページで解説しています。
ON DELETE CASCADE で関連する行をまとめて削除する
親テーブルと子テーブルを外部キーでつないでいる場合、親を削除しただけでは子が孤立して残ってしまいます。SQLite に連鎖削除を指示しておけば、これを防げます。
著者を削除すると、その著者の本もまとめて消えます。もし ON DELETE CASCADE が無ければ、同じ DELETE は外部キーが無効ならそのまま通って孤児レコードが残りますし、有効なら制約エラーで失敗します。
ここで一番ハマりやすいのが、SQLite では外部キーがデフォルトで無効 という点です。接続ごとに PRAGMA foreign_keys = ON; を実行する必要があります。このプラグマを設定し忘れると、ON DELETE CASCADE は何も言わずに無視され、本のレコードはそのまま残ります。アプリ側のドライバが自動で設定してくれたり、オプションで指定できたりするので、自分が使っているドライバの仕様を確認しておきましょう。
連鎖削除に関連して覚えておきたい他の動作も紹介します。ON DELETE SET NULL(外部キーを NULL にクリア)、ON DELETE RESTRICT(子レコードがあれば削除を拒否)、ON DELETE NO ACTION(デフォルト。ほとんどの場合 RESTRICT と同じ挙動)です。
LIMIT 付き DELETE(コンパイル時オプション)
ビルドによっては DELETE ... LIMIT が使える SQLite もあり、巨大なテーブルを少しずつバッチで削っていきたいときに便利です。
DELETE FROM logs
WHERE created_at < '2024-01-01'
ORDER BY created_at
LIMIT 1000;
この機能を使うには、SQLite が SQLITE_ENABLE_UPDATE_DELETE_LIMIT 付きでコンパイルされている必要があります。公式バイナリや主要な言語バインディング（Python の sqlite3、Node の better-sqlite3 など）では有効になっているので、基本的にはそのまま使えます。もし無効になっているビルドを使っていると構文エラーになるので、その場合はサブクエリで代用しましょう。
DELETE FROM logs
WHERE id IN (
SELECT id FROM logs
WHERE created_at < '2024-01-01'
ORDER BY created_at
LIMIT 1000
);
バッチで削除すれば1回あたりのトランザクションを小さく保てるので、他の接続が同じデータベースを読んでいるときでも影響を抑えられます。
大量削除はトランザクションで囲む
DELETE は暗黙的にトランザクションとして実行されるので、対象の行は「全部消える」か「1件も消えない」かのどちらかになります。とはいえ、まとまった件数を削除する場合は明示的にトランザクションで囲んでおくと、結果がおかしいと思ったときに ROLLBACK で取り消せて安心です。
ROLLBACK を実行すれば、削除は完全に取り消されます。実際の作業では、件数を確認してから COMMIT する流れになります。さらに、大量の行を1文ずつ削除する場合は、トランザクションでまとめると劇的に速くなります。ループを BEGIN/COMMIT で囲むことで、削除のたびに fsync が走るのを避けられるからです。
「削除されない」よくある誤解
つまずきやすいポイントをいくつか挙げておきます。
DELETE FROM table;はテーブルを空にするだけで、テーブル自体は消えません。テーブルそのものを削除したいならDROP TABLE table;を使います。これが sqlite で全件削除する際の正しいやり方で、他のDBでいう truncate の代わりに相当します。DELETEを実行してもデータベースファイルは縮みません。空いたページは再利用のために「空き」とマークされるだけです。ディスク容量を実際に解放するにはVACUUM;を実行します(パフォーマンスの章で扱います)。- ある行を削除しても、他のテーブルの子行までは消えません。連鎖削除を効かせるには
ON DELETE CASCADEを設定したうえで、PRAGMA foreign_keys = ON;で外部キー制約を有効化しておく必要があります。 DELETEが1件もヒットしなくてもエラーにはなりません。changes() = 0で正常終了するだけです。削除されたかどうか知りたい場合は、影響行数を自分で確認しましょう。
次は UPSERT
実際のところ、「削除したい」のではなく「無ければ INSERT、あれば UPDATE したい」というケースは少なくありません。SQLite ではこれを UPSERT と呼び、ON CONFLICT 句を使えば3つの文に分けずに1文で書けます。次の章で詳しく見ていきましょう。
よくある質問
SQLiteで1行だけ削除するには？
基本構文は DELETE FROM テーブル名 WHERE 条件; です。WHERE 句で削除対象の行を絞り込みます。たとえば DELETE FROM users WHERE id = 7; なら、id が 7 のユーザー1件だけが消えます。WHERE を書き忘れるとテーブル内の全行が消えてしまうので要注意です。
SQLiteでテーブルの全行を削除するには？
WHERE を付けずに DELETE FROM テーブル名; を実行すればOKです。SQLiteには TRUNCATE 文が存在しないので、これが事実上の全件削除になります。内部的には「truncate optimization」と呼ばれる最適化が効くので速度面の心配もありません。AUTOINCREMENT のカウンタもリセットしたい場合は、続けて sqlite_sequence から該当行を削除してください。
SQLiteで関連テーブルにも連鎖削除（カスケード）できますか？
できます。外部キー制約に ON DELETE CASCADE を付けたうえで、PRAGMA foreign_keys = ON; を実行して外部キーを有効化しておく必要があります。SQLiteはデフォルトで外部キーがオフになっており、このPRAGMAを忘れるとカスケード設定が黙って無視されてしまうので注意です。
削除した行の内容を確認するには？
RETURNING 句を付けるのが便利です。たとえば DELETE FROM users WHERE active = 0 RETURNING id, email; のように書けば、SELECT と同じ感覚で削除された行のデータを取得できます。ログ記録、Undo機能の実装、本当に消したかった行だけが消えたかの確認などに重宝します。
