Deferred primary keys; work for updates?
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 4 of 4

Thread: Deferred primary keys; work for updates?

  1. #1
    Join Date
    Mar 2006
    Posts
    74

    Deferred primary keys; work for updates?

    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..?

  2. #2
    Join Date
    Apr 2001
    Location
    Czechia
    Posts
    712
    You have the constraint just deferrable, not deferred.
    You defer the constraint using the "SET CONSTRAINT ... DEFERRED" command.
    After that, you will be able to swap the values.

    Changing values in a primary key is not a good idea, by the way.
    Ales
    The whole difference between a little boy and an adult man is the price of toys

  3. #3
    Join Date
    Mar 2006
    Posts
    74
    Hokay, I'll try that - i hoped that deferrable primary keys would automatically be deferred upon beginning a transaction involving updates to them.. For a single user system, is there any difference beteen deferring and en/disabling?

    Do you have any comment on what happens when I say:

    UPDATE table SET id = 1 WHERE id = 2
    UPDATE table SET id = 2 WHERE id = 1

    Could this conceptually make the following problem:
    id, data
    1, whatever1
    2, whatever2

    id, data
    1, whatever1
    1, whatever2

    id, data
    2, whatever1
    2, whatever2

    PK Violated..



    Nnnnhhhyeah, I got a bit of a problem with having to change the PKs because its part of still setting up the process. I'd love to show you the query that this will be used with. Suffice to say its a management report that has 85 sets of unrelated information on one page. Each item has an ID, a query (unioned with 84 other queries) produces values for that entry for all clients and generates a huge cross-tabbable data set that is then combined with a table holding factors that the summated values must be multiplied by, to work out costings of everything. Not all clients have specific values, so a set of defaults must be used.. THe hardest challenge to overcome so far, was to turna table that has a mixture of generic and client specific values, into a reference list with which to multiply my values by..

    CHanging the IDs is necessary at this stage because the structure of the report is shifting and being re-grouped from 5 major groups to around 9 (each groups are totalled with an analytic) so the IDs need to be changed.. Nuisance.

    I personally loathe that the database must run an 85-long union query, and each segment isnt light, so I'm trying to combine and prune as much as possible..

  4. #4
    Join Date
    Apr 2001
    Location
    Czechia
    Posts
    712
    For a single user system, is there any difference beteen deferring and en/disabling?
    Yes, of course. Disabled=not functional at all, deferred=funtional, but postponed to the end of the transaction.

    Do you have any comment on what happens when I say:

    UPDATE table SET id = 1 WHERE id = 2
    UPDATE table SET id = 2 WHERE id = 1

    Could this conceptually make the following problem:
    id, data
    1, whatever1
    2, whatever2

    id, data
    1, whatever1
    1, whatever2

    id, data
    2, whatever1
    2, whatever2

    PK Violated..
    What about:
    UPDATE table SET id = -999 WHERE id = 2
    UPDATE table SET id = 2 WHERE id = 1
    UPDATE table SET id = 1 WHERE id = -999

    Replace -999 with any suitable value that normally cannot exist in the ID column.
    No PK deferring needed ...
    Ales
    The whole difference between a little boy and an adult man is the price of toys

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width