1対多の関係を持つテーブルのデータを削除するループのロジック

Question

KotlinとSpring Bootで開発中です。

題名の通り、データ削除のロジックについて質問です。

1:Aテーブル
多:Bテーブル
の関係2つのテーブルがあります。

Bテーブルのレコードが5日以上古い場合は、都度削除するようにしようとしています。
その際、外部キーとして持っているAテーブルのレコードも削除する仕様です。

現在の実装

Bテーブルから5日以上前のレコードをSQLで全部引っ張ってくる。
↓
引っ張ってきたレコードの数でループを回す
↓
Bテーブルのidと回しているレコードのidが合致したら削除
↓
Aテーブルのidと回しているレコードの`a_id`が合致したら削除

という感じで作ってみました。
しかし、よくよく考えるとBテーブルは多ですので、Aを削除する前に紐付いているBのレコードは全部削除してからAを削除しないと外部キー制約で確実にループがコケていしまいます。

こういった場合はどういうロジックで削除していくのがいいのでしょうか？
ループの中にifで分岐させるのも考えたのですが、そもそも古いレコード分ループが回るのでしっくりきませんでした。

どなかた教えて頂ける方、よろしくお願い致します。

Comments

[Orlofsky]

同じSQLでも方言が大きいですから、どのデータベースを使うのかを質問のタグで明示した方が適切なコメントが付き易いです。
https://www.ashisuto.co.jp/corporate/column/technical-column/detail/1197236_2274.html Aテーブル、BテーブルのCREATE TABLE文も載せた方が良いです。

[TM3tmlab]

条件を一つ確認したいです。「AテーブルとBテーブルは必ず1対以上が同時に作成される」かどうか。一対多といっても 1対0...n なのか 1対1...n なのかで変わるので。

[tomuziso]

失礼しました。MySQLという前提でした。また、条件については1対0...nという条件でした。情報に不足があり申し訳ありません。

Answer 1

データベースが何か書いてありませんが、ON DELETE CASCADE付きで外部キーを設定してあれば親テーブルの5日以上経過したデータをDELETEするSQLを1行実行するだけで子テーブルもDELETEできます。
FOREIGN KEY 外部キー ON DELETE CASCADE の有無での動作確認

無駄にLOOPする必要はありません。
SQLはきちんと勉強してください。

Comments

[tomuziso]

回答ありがとうございます。
ON DELETE CASCADE 付きであれば、親を削除するだけで子が自動的に削除されるのですね。
勉強不足でした。こちらであれば無駄にループをする必要はありませんね。

設定しなかった場合は子を全部削除してから親を削除する方法を取ることで解決しそうですね。ありがとうございます。

[tomuziso]

回答ありがとうございます。
ON DELETE CASCADE 付きであれば、親を削除するだけで子が自動的に削除されるのですね。
勉強不足でした。こちらであれば無駄にループをする必要はありませんね。

設定しなかった場合は子を全部削除してから親を削除する方法を取ることで解決しそうですね。ありがとうございます。

Answer 2

Aテーブルに紐づくBテーブルのレコードのうち1件でも指定日数を超えたら、では既出の通りです。

Aテーブルに紐づくBテーブルの"全て"のレコードが 5日 を超えている場合に削除するSQLを作ってみました。
ON DELETE CASCADE は必要ですのでご留意ください。

DELETE FROM A
WHERE id IN (
  SELECT DISTINCT t1.a_id
  FROM
    (
      SELECT a_id
      FROM B
      WHERE DATE_SUB(CURDATE(),INTERVAL 5 DAY) > CREATE_DATE
    ) t1 
    LEFT OUTER JOIN (
      SELECT a_id
      FROM B
      WHERE DATE_SUB(CURDATE(),INTERVAL 5 DAY) <= CREATE_DATE
    ) t2
    on t1.a_id = t2.a_id
  WHERE t2.a_id IS NULL
);

DATE_SUB(CURDATE(),INTERVAL 5 DAY) 当日から5日前の日付を作る
t1 5日より過去に作成されたレコードの集合
t2 5日以内に作成されたレコードの集合
t1 LEFT OUTER JOIN t2 と t2.a_id IS NULL によって 5日より過去に作成されたレコードがあり、5日以内に作成されたレコードがない。

Answer 3

1.Bテーブルから5日以上前のレコードを削除
2.AテーブルからBレコードが1件も紐付いていないレコードを削除

Comments

[tomuziso]

Bテーブルのレコードを削除するために、Aの作成日を見てそちらから紐付いたBのレコードを削除する、という方法を取れば良さそうですね。ありがとうございます。