中間テーブルにデータを挿入する際に選択するべきな妥当なロジックを知りたい

前提・実現したいこと

前提

htmlのselectタグにてユーザーが選択した複数項目を中間テーブルに追加するという前提で、①選択項目のデータがテーブルに無ければ追加、②あればそのまま、③選択項目に無いデータがテーブルに存在すれば削除したい。

その上で、以下(1)~(4)の方法を考えたのですが、一般的に推奨される方法とその理由をお教え頂きたいです。
※前提として上記の考え方が誤っていたらその点ご指摘頂きたいです。また、その他推奨される方法があればお教え頂きたいです。

テーブル構成例

解決案

※更新データはuser_idと、department_id(配列)で取得しています。

(1) REPLACE INTO を使用する(公式)

テーブル内の古い行に、PRIMARY KEY または UNIQUE インデックスに関して新しい行と同じ値が含まれている場合、その古い行は新しい行が挿入される前に削除されます。

今回のように中間テーブルの外部キーが非uniqueの場合、使用できないのではと思う。
例：次のデータ(user_id = 1 & department_id = 4)を挿入しようとした際に、id=1のデータがdeleteされinsertされる挙動になると考える。

(2) INSERT ... ON DUPLICATE KEY UPDATEを使用する(公式)

このやり方だと、③選択項目に無いデータがテーブルに存在すれば削除が行われない為、別途deleteのクエリを追加する必要があると考える。

(3) delete->insert

処理したいuser_idに合致するデータを全てdeleteし、新規に挿入する。
→ 一番効率的？

(4) 条件分岐

配列のdepartment_idがテーブルに存在すればスルー
配列のdepartment_idがテーブルに存在しなければ新規追加
配列に存在しないdepartment_idがテーブルに存在すれば削除
->原始的な気がする

使っているツールのバージョンなど補足情報

windows
mysql 8.0
php 7.2
codeigniter 3

該当コードはcodeigniterのクエリビルダor生SQLを想定

行動規範の内容に同意します

回答1件

ベストアンサー

質問は修正できます。
MySQL 外部キー制約を活用するで
ON DELETE CASCADE ON UPDATE CASCADE も設定して、実際にSQLでCREATE TABLE からデータをINSERT, UPDATE, DELETE して動作確認したログを整理し、質問に追記して疑問点を再確認しては？

投稿2022/05/03 08:43