

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になってしまうので、ユニーク制約違反のエラーが発生します。

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


CC BY-SA 3.0
2017-06-30T06:52:15.087
35995
2017-09-21T13:29:46.670
8078
question
jpa
一時的にユニーク制約を違反してしまう場合のベストプラクティス


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

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

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

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

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

参考になれば幸いです。


CC BY-SA 3.0
2017-07-02T08:19:38.390
36028
2017-07-02T08:19:38.390
20098
35995
answer


一つの考えですが、ユニーク制約の場合はNULLを持つこと許容します。NOT NULL制約がないなら  
(1) UPDATE X SET ORDER = NULL WHERE ID = 'B'  
(2) UPDATE X SET ORDER = 2 WHERE ID = 'C'  
(3) UPDATE X SET ORDER = 3 WHERE ID = 'B'  
で対応できるかもしれません。

ユニーク制約の削除・再作成、無効化・有効化だとUNIQUE INDEXの作り直しになるのである程度の負荷が予想されます。それは避けられるかと。  
今回の場合は2行についてDELETE、INSERTでもいいかもしれません。  
IDにインデックスがなければそのほうが手間もシステム負荷も少ないと思います。  
一方でIDにインデックスがあるような場合はメンテナンスコストが少なくすむ可能性はあるのではないかと思います。

ご参考になれば。


CC BY-SA 3.0
2017-09-21T13:29:46.670
38095
2017-09-21T13:29:46.670
19743
35995
answer