We are developing using JPA.
The target DB is designed to work with SQL Server, Oracle, or PostgreSQL.
(I use JPA, so I don't make any difference in implementation.)
The column ORDER appears in Table X where you want to save a master.
It contains the order of arrangement when displaying all the masters of Table X.
However, we do not want to duplicate the ORDER numbers, so we have unique constraints.
This alignment order can be replaced by drag-and-drop on the screen, but when you register, you update the numbers in the ORDER column for multiple records.
At this time, a violation of the unique constraint occurred, and I am troubled.
For example,
ID ORDER
A1
B2
C3
On the other hand, suppose you replace B and C.
In that case, if you make a query without thinking about it, it will look like this.
*Actually implemented using JPQL.
(1) UPDATE X SETORDER=3 WHERE ID='B'
(2) UPDATE X SET ORDER=2 WHERE ID='C'
However, running (1) results in a unique constraint violation error because both B and C have ORDER.
I think these requests are relatively common, but how do you solve them?
jpa
There are three possibilities.(I couldn't think of this as the best because I think it's good or bad.)
I think 1 is pretty.However, if you write in SQL, you will have to combine two tables, so it may be troublesome.
I think 2 is a common practice if only applications operate tables.(If you swap within the same transaction, you can't see the duplication from the outside.)
3. There are times when we do it, but since there is a difference in performance depending on DBMS, is it a way to use it when DBMS is decided?
I hope it will be helpful.
It's one idea, but if it's a unique constraint, it's acceptable to have NULL.
without NOT NULL constraints
(1) UPDATE X SET ORDER=NULL WHERE ID='B'
(2) UPDATE X SETORDER=2 WHERE ID='C'
(3) UPDATE X SETORDER=3 WHERE ID='B'
It may be possible to use the .
Removing, re-creating, disabling, and enabling unique constraints will result in UNIQUE INDEX being re-created, so some load is expected.I wonder if that can be avoided.
In this case, DELETE and INSERT may be fine for the two lines.
If the ID does not have an index, I think it will take less effort and system load.
On the other hand, if the ID has an index, I think maintenance costs may be low.
For your information.
© 2024 OneMinuteCode. All rights reserved.