views:

30

answers:

2

I am using Microsoft SQL Server and I have a master-detail scenario where I need to store the order of details. So in the Detail table I have ID, MasterID, Position and some other columns. There is also a unique index on MasterID and Position. It works OK except one case: when I have some existing details and I change their order. For example when I change a detail on position 3 with a detail on position 2. When I save the detail on position 2 (which in the database has Position equal to 3) SQL Server protests, because the index uniqueness constraint.

How to solve this problem in a reasonable way?

Thank you in advance
Lukasz Glaz

A: 

you could just remove the unique constraint (but leave an index key) on the order column, and ensure uniqueness in your code if necessary.

oedo
Coming from an EDI background, I can say this isn't always practical. It would usually work, but there are instances where it's way more trouble than it's worth. Mark's solution works very well.
AllenG
+1  A: 

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 of 2 to a temporary number (e.g. 99). So it goes like this:

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

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

Update: BTW - one way to deal with the "multiple users may be changing the order" issue is to do what I do: give each user a numberical ID and then add this to the temporary number (my staff ID is actually the Unique Identity field ID from the staff table used to gate logins). So, for example, if your positions will never be negative, you might use -1000 - UserID as your temporary value. Trust me on one thing though: you do not want to just assume that you'll never have a collision. If you think that and one does occur, it'll be extremely hard to debug!

Update: GUZ points out that his users may have reordered an entire set of line items and submitted them as a batch - it isn't just a switch of two records. You can approach this in one of two ways, then.

First, you could change the existing sort fields of the entire set to a new set of non-colliding values (e.g. -100 - (staffID * maxSetSize) + existingOrderVal) and then go record-by-record and change each record to the new order value.

Or you could essentially treat it like a bubble sort on an array where the orderVal value is the equivalent of your array index. Either this makes perfect sense to you (and is obvious) or you should stick with solution 1 (which is easier in any event).

Mark Brittingham
I was just about to post this answer. Beat me by 30(ish) seconds.
AllenG
@AllenG - I hear you...I can't tell you how many times I've hit the Submit button only to find I've been beaten to the punch. There is a bit of "get it out there first" pressure on SO.
Mark Brittingham
The problem is that a user may do many changes in scope of the order of details and then those changes are saved in one batch.
GUZ
GUZ - you just have to work your way backward then. See my update above.
Mark Brittingham