Spencer Simpson
asked on
SQL update values that participate in a unique key index
I recently found to my surprise that a query such as
worked, even though displayorder had a unique key index and all of the values over 50 were contiguous.
A subsequent
worked, although (of course, since there was also a 50) another such decrementing update violated the constraint and failed.
I was surprised because there isn't anything to indicate that the rows would be processed in an order that wouldn't violate the constraint.
When the update processes the record with a value of 55 and there's already a record with a value of 56, updating record 55's value to 56 creates a temporary duplication (at least until record 56 is processed).
But it works.
My question is, can you rely on this? I'm using SQL Server 2008 R2, but I've no idea if other SQL implementations would have this behavior.
Does this work because constraints and indexes only enforced on a commit?
If, so, I suppose that when a statement is run outside of a specific transaction, the SQL engine wraps it in a mini-transaction of its own?
update mytab set displayorder = displayorder+1 where displayorder > 50;
worked, even though displayorder had a unique key index and all of the values over 50 were contiguous.
A subsequent
update mytab set displayorder = displayorder-1 where displayorder > 50;
worked, although (of course, since there was also a 50) another such decrementing update violated the constraint and failed.
I was surprised because there isn't anything to indicate that the rows would be processed in an order that wouldn't violate the constraint.
When the update processes the record with a value of 55 and there's already a record with a value of 56, updating record 55's value to 56 creates a temporary duplication (at least until record 56 is processed).
But it works.
My question is, can you rely on this? I'm using SQL Server 2008 R2, but I've no idea if other SQL implementations would have this behavior.
Does this work because constraints and indexes only enforced on a commit?
If, so, I suppose that when a statement is run outside of a specific transaction, the SQL engine wraps it in a mini-transaction of its own?
ASKER
Since the update statement's WHERE contained only DisplayOrder >50 without an upper bound, then you don't have to worry about a "final" value, since it would be greater than all of the existing values in the table.
The only real risk is an overflow (say for a 16-bit int column that had a value of 32767 or a decimal (4,0) with a value of 9999).
The only real risk is an overflow (say for a 16-bit int column that had a value of 32767 or a decimal (4,0) with a value of 9999).
Exactly. And since the subtraction already had 50, 51, etc., the remaining key had nowhere to go but to the duplication reject.
ASKER
Which was expected (and desirable). And if my incrementing update had specified an upper bound (call it UB) then it would also have failed if there had been a record with UB+1.
But it didn't have an upper bound, so there would never have been any concern about a "final" value to overwrite.
So what is it that prevents failure while the update is being processed?
But it didn't have an upper bound, so there would never have been any concern about a "final" value to overwrite.
So what is it that prevents failure while the update is being processed?
Usually this is a rare occurrence. In most cases, sequence fields continually ascend. I've been a DBA, developer, etc., for over 35 years, and I've never really faced any issue with that.
However, that is quite a find on your part. And hopefully my explanation and piloting of that discovery has helped.
And if you have a business need for something like this, the best course may be to offload the table into a temp table, resequencing as you go, then truncate the source table, then reload it.
However, that is quite a find on your part. And hopefully my explanation and piloting of that discovery has helped.
And if you have a business need for something like this, the best course may be to offload the table into a temp table, resequencing as you go, then truncate the source table, then reload it.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Beyond being the most practical way, it's the most desirable way in an abstract sense.
I was hoping that it wasn't restricted to just SQL Server; I'd been made cautious because of the row-by-row behavior of ROWNUM in an Oracle select statement.
I was hoping that it wasn't restricted to just SQL Server; I'd been made cautious because of the row-by-row behavior of ROWNUM in an Oracle select statement.
Quite true, it's a more desirable approach overall.
Also true about Oracle. Oracle is, by its nature, far more row oriented overall; basically everything in Oracle is done by a cursor (which if, of course, nothing like a cursor in SQL Server, just to keep it all very confusing :-) ). Another quick example: triggers in Oracle can be specified to run by row, not so in SQL Server.
But Oracle has, I believe, true deferred resolution on indexes that can allow temporary anomalies that even SQL Server can't handle. So, I would expect Oracle (and UDB/DB2, btw) to handle these types of UPDATEs just fine. [However, I haven't been a professional DBA on Oracle since 8.0, so I can't speak in more detail about Oracle's current approach to this.]
Also true about Oracle. Oracle is, by its nature, far more row oriented overall; basically everything in Oracle is done by a cursor (which if, of course, nothing like a cursor in SQL Server, just to keep it all very confusing :-) ). Another quick example: triggers in Oracle can be specified to run by row, not so in SQL Server.
But Oracle has, I believe, true deferred resolution on indexes that can allow temporary anomalies that even SQL Server can't handle. So, I would expect Oracle (and UDB/DB2, btw) to handle these types of UPDATEs just fine. [However, I haven't been a professional DBA on Oracle since 8.0, so I can't speak in more detail about Oracle's current approach to this.]
For instance, if you run both queries together, they will work. If you only run the second query, it will violate the primary key.
So the rule is, there must be room for the FINAL primary value to change to, in order for all of them to work. That intelligence in SQL Server is impressive.
I used the following as an exercise:
Open in new window