I have a table that has a deferred primary key, created as:

ALTER TABLE "GTP"."ZEB_ROW_CONFIG" ADD ( CONSTRAINT "PK_ZEB_ROW_CONFIG" PRIMARY KEY ("DIV", "INST", "LBOUND", "MAJOR", "MINOR") DEFERRABLE USING INDEX "GTP"."PK_ZEB_ROW_CONFIG" ENABLE VALIDATE )


When I try to use this in my VB.NET program, however, I begin the transaction, then begin an update that swaps 2 values.. i.e.

Update row set pkcol3 = 3 where pkcol1 = blah and pkcol2 = blah and pkcol3 = 1

later this should occur:
Update row set pkcol3 = 1 where pkcol1 = blah and pkcol2 = blah and pkcol3 = 3

Then the transaction should complete.



-
I hoped that deferrable PKs would behave such that:

The new value of row "blah, blah, 1" would be "blah, blah, 3"
The new value of row "blah, blah, 3" would be "blah, blah, 1"


i.e. the update would search using the original value, and set a pending new value to finalise when the transaction completes.
What Im actually finding is that as soon as the first update is fired:


Update row set pkcol3 = 3 where pkcol1 = blah and pkcol2 = blah and pkcol3 = 1

because "blah, blah, 3" already exists when I try to update "blah, blah, 1" to become "blah, blah, 3" a unique constraint violation occurs.. I thought this was supposed to be deferred? The transaction works, as all the data up to now is rolled back..

What is happening, and also , is it possible to swap two values in this way..?