【これだけ】SQLでのデータの削除【現役エンジニアが解説】
今回はSQLでのデータの削除について、事例を交えて簡単に解説していきます。
物理削除
まず、ユーザ情報が入っている以下のusersテーブルというものがデータベースにあると仮定します。
| user_id | name |
|---|---|
| 1 | 山田太郎 |
| 2 | 田中花子 |
| 3 | 小林一郎 |
このusersテーブルから、user_idが1の山田太郎のレコードを削除する場合、以下のSQLを実行します。
DELETE FROM users WHERE user_id = '1';
SQLの参考書では、レコードの取得がSELECT、追加がINSERT、更新がUPDATE、削除がDELETEと教わると思います。
今回もレコードの削除をしたいため、その基本に則って、DELETE文を使ってusersテーブルからuser_idが1のレコードを削除しています。
このように、DELETE文を使って完全にレコードを削除することを物理削除と言います。
論理削除
システムの要件によっては、世代管理でログを残すことや一度削除したものを復元することが求められることがあります。
このような場合に前述の物理削除では、レコードを完全に消してしまうため、要件を実現することが困難になってしまいます。
そこで論理削除という概念が活躍することになります。
論理削除では該当のテーブルに、まず削除フラグを持たせることになります。
論理削除を実現するためには、先程のusersテーブルに削除フラグであるdelete_flgのカラムを追加しておきます。
| user_id | name | delete_flg |
|---|---|---|
| 1 | 山田太郎 | 0 |
| 2 | 田中花子 | 0 |
| 3 | 小林一郎 | 0 |
デフォルトではdelete_flgは0の値を持たせ、SELECTする際はdelete_flgの条件を0とすれば削除されていないレコードだけを取得することができます。
SELECT * FROM users WHERE delete_flg = '0';
このような仕様にした上で、削除する際はdelete_flgを1に設定するだけでOKです。
UPDATE users SET delete_flg = '1' WHERE user_id = '1';
したがって、この場合の削除は実際に消すのではなく、delete_flgを1に変更する処理となるため、上記のようにUPDATE文を実行することになります。
その他の削除
物理削除を行う前に、テーブル定義がほぼ同一のテーブルにINSERTすれば、論理削除でなくてもデータの退避は可能です。
削除したレコードを復元する必要がないような場合には必ずしも論理削除する必要がないため、このような方法でも良いと思います。
INSERT INTO bk_users SELECT * FROM users WHERE user_id = '1'; DELETE FROM users WHERE user_id = '1';
結局、ほとんど余分なレコードがひとつのテーブルに同居していると、それだけSELECTの際にパフォーマンスが落ちるため、インデックス等最適化する必要性も出てくるため、シンプルに作るのであれば物理削除でも良いでしょう。
一方で、物理削除のリスクは、他のテーブルでも使われているマスタの情報を削除してしまうことですが、これはデータの整合性を保つため、外部キーを設定して削除を制限することができます。
ただし、外部キーは子テーブル側でテーブルのCREATE時にFOREIGN KEYを設定することで実現することができますが、容易に削除することができなくなってしまうため、開発者の間では敬遠される傾向にあります。