外部キーが使用できない環境で参照整合性を保証する方法

Question

外部キーが使用できない環境でどのようにして参照整合性を保証したら良いかというご質問です。
外部キーが使用できない主な理由は、MySQLのパーティショニング機能を利用するためです。

外部キーを使用しない場合、下記のようにアプリケーション側で参照整合性を保証する必要があるかと思います。

• INSERT → 親の行が存在することを確認する
• DELETE → 子の行が存在しないことを確認する

例えばECサイトで、登録した商品情報を物理削除する場合、どのようにアプリケーションで実装するのがベターでしょうか？
下記のように親テーブル（本件の商品テーブル）に紐づく子テーブルが数多く存在する場合、デッドロックや性能面で問題がでてくる思っています。

■親テーブル
- 商品

■子テーブル
- 注文詳細
- 商品画像
- 商品バリエーション
- 商品レビュー
- お気に入り商品
・
・
・

外部キーを使用している環境であれば、例外をキャッチしてハンドリングするのが一般的だと思いますが、本ケースの場合どうするのがベターなのか知りたいです。

1. それぞれのテーブルに対して子行の存在確認すると同時にロックを取得（存在しなかった事実確認後のデータ挿入を防止するため）
2. 全テーブルで子行が存在しなければ親行を物理削除、もし１テーブルでも子行が存在してれば論理削除

という単純な方法しか思いつかないのですが、ベターな方法が御座いましたらご教示いただきたいです。

よろしくお願いします。

Answer 1

業務上の具体的な問題を列挙して、１つずつ定期的にチェックしていくのが良いと思います。

これであれば、外部キーでチェックできないケース、例えば商品画像内容が間違ってる、
バリエーションが足らない、などもチェック対応が可能になります。

チェックについては、SQLのみでチェックできる方法を講じるのが良いと思います。
保守がしやすくなります。