一時的にユニーク制約を違反してしまう場合のベストプラクティス


JPAを使って開発しています。  
対象DBは、SQL Server、Oracle、PostgreSQLのいずれでも動作可能な設計です。  
（JPAを使っているので、実装上は特に区別しません）

あるマスタを保存するテーブルXに、`ORDER`という列があります。  
テーブルXのマスタを全て表示するときの並び順が入っています。  
ただ、`ORDER`の数値は重複させたくないので、ユニーク制約を設けています。

この並び順は、画面上でドラッグ＆ドロップで入れ替えることができるのですが、登録するときには複数のレコードに対して`ORDER`列の数値を更新します。  
この時、ユニーク制約の違反が発生してしまい、困っています。

例えば、

[code]

    ID     ORDER
    A      1
    B      2
    C      3
    
[/code]

これに対して、BとCを入れ替えるとします。  
その場合のクエリは、何も考えずに作ると次のようになると思います。  
※実際にはJPQLを使って実装しています。

[code]

    (1) UPDATE X SET ORDER = 3 WHERE ID = 'B'
    (2) UPDATE X SET ORDER = 2 WHERE ID = 'C'
    
[/code]

しかしこれでは、(1)を実行すると、BもCも`ORDER`が3になってしまうので、ユニーク制約違反のエラーが発生します。

こういった要求は割とよくあると思うのですが、どのように解決するものなのでしょうか？




3通り考えられます。(一長一短と思うので、これがベスト、というのは浮かびませんでした)

  1. テーブル`X`を正規化して、データを持つテーブル`XDATA`と各行の順序を表す`XORDER`テーブルに分離する。`XORDER`は`ORDER`と`XDATA`の行IDから構成し、`ORDER`のみユニークキーとすれば、順序の入れ替え時にID列を書き換えても一意性制約違反にならない。
  2. テーブル`X`の`ORDER`列の一意性制約は外し、アプリケーションでユニークになるよう制御する。
  3. 順序を入れ替える2行を両方削除して`ORDER`の内容を入れ替えて挿入しなおす。

1はきれいと思います。ただ、SQLで書く場合、2テーブル結合することになるので、面倒かも知れません。

2はテーブルを操作するのがアプリケーションだけであれば、よく取られる手法かと思います。(同一トランザクション内で入れ替えれば、重複は外からは見えないので)

3はやることもありますが、DBMSによって性能差が出るので、DBMSが決まっている際に採る方法でしょうか。

参考になれば幸いです。

