Updating multiple rows that conflict with a unique index

Question

I am using Microsoft SQL Server, and I have a basic parts scenario where I need to maintain the order of the parts. Therefore, the Detail table has ID, MasterID, Position, and some other columns. There is also a unique pointer to MasterID and Position. It works fine, except for one case: when I have some existing parts and I change their order. For example, when I change a part in position 3 with a part in position 2. When I save a part in position 2 (which has a position equal to 3 in the database), SQL Server protests, as it limits the uniqueness of the index.

How to solve this problem in a reasonable way?

Thank you in advance Lukas Eye

Answer 1

This is a classic problem, and the answer is simple: if you want to move item 3 to position 2, you must first change the sort column 2 to a temporary number (for example, 99). So this happens as follows:

Move 2 to 99 Move 3 to 2 Move 99 to 3 

However, you must be careful that your temporary value is never used in normal processing, and that you respect multiple threads, if applicable.

Update : BTW - one of the ways to solve the “multiple users can change the order” problem is to do what I do: give each user a number identifier, and then add it to a temporary number (my state identifier is the identifier of the Unique identifier field from the table employees used to enter the gateway). So, for example, if your position is never negative, you can use -1000 - UserID as your temporary value. Believe me, at least one thing: you don’t just want to assume that you will never have a collision. If you think this is happening, then it will be very difficult to debug!

Update : The GUZ indicates that its users could reorder the entire set of positions and sent them as a package - this is not just a switch of two entries. Then you can approach this in one of two ways.

Firstly, you can change the existing sort fields of the entire set to a new set of non-colliding values ​​(for example, -100 - (staffID * maxSetSize) + existingOrderVal), and then go to record by record and change each record to a new order value.

Or you could essentially treat it like sorting bubbles in an array, where the value of orderVal is equivalent to your array index. Either this makes perfect sense for you (and this is obvious), or you should stick to solution 1 (which is easier anyway).

Answer 2

you can simply remove the unique restriction (but leave the index key) in the order column and ensure that the code is unique if necessary.