Results 1 to 11 of 11
  1. #1
    Join Date
    Sep 2006
    Posts
    57

    Unanswered: change primary index value

    Is there any way to change the value of a primary key value?

  2. #2
    Join Date
    Nov 2005
    Location
    San Francisco, CA
    Posts
    506
    Quote Originally Posted by Luke101
    Is there any way to change the value of a primary key value?
    Use cascading Update to change a primary key and its related foreign key.
    Success is the ability to go from one failure to another with no loss of enthusiasm.
    - Sir Winston Churchill
    Joydeep

  3. #3
    Join Date
    Sep 2006
    Posts
    57
    is cascading update slow?

    It just dawned on me that if I changed a primary key I would have to change every PK in all rows.

    Will there be a performance hit if this is done?

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    in all rows of which table? you wouldn't need to do it if you're talking about ON UPDATE CASCADE

    UPDATE takes as long as UPDATE takes, but if there are cascading updates, those will obviously take time too

    could you give a more specific example, please
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    Sep 2006
    Posts
    57
    OK for example:

    Code:
    PK      Product         Price       
    1        SQL Book       $99.99
    2        ASP Book       $89.99
    3        C# Intro        $99.99
    4        Computer       $500.00
    5        Hard Drive      $150.00
    For example, asp book has matched one of my queries and I want to switch ASP book and SQL book PKs. So the table now looks like this:

    Code:
    PK      Product         Price    
    1        ASP Book       $89.99
    2        SQL Book       $99.99
    3        C# Intro        $99.99
    4        Computer       $500.00
    5        Hard Drive      $150.00
    is this possible?

  6. #6
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    i suppose it's possible, but by far the more important question is: why would you want to do this!!!!

    no offence, but do you understand what the purpose of the PK is?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  7. #7
    Join Date
    Sep 2006
    Posts
    57
    well..I am guessing that if the PK s are already in order either by alphabet or price,etc i would not have to use the SORT BY in the SELECT command which I can only conclude slows the query down.

  8. #8
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    use ORDER BY if you want a specific sequence

    manipulating a numeric surrogate primary key to maintain a sequence is like trying to empty a swimming pool with a teaspoon

    add an index to the table on the price column, and ORDER BY will be efficient
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  9. #9
    Join Date
    Sep 2006
    Posts
    57
    So, ORDER BY carries little overhead even if I have over 5000 rows? Right now, ORDER BY is very fast but I expect the database to get bigger in the future.

  10. #10
    Join Date
    Nov 2005
    Location
    San Francisco, CA
    Posts
    506
    Quote Originally Posted by Luke101
    well..I am guessing that if the PK s are already in order either by alphabet or price,etc i would not have to use the SORT BY in the SELECT command which I can only conclude slows the query down.
    SORT BY???
    You mean ORDER BY,I don't think that using ORDER BY will give you a performance hit if you have indices on those fields,well in that case an clustered index can help you,if you want to keep the order in which the data is inserted on that PK.
    Creating proper Indices helps a lot in the query performance...
    Success is the ability to go from one failure to another with no loss of enthusiasm.
    - Sir Winston Churchill
    Joydeep

  11. #11
    Join Date
    May 2004
    Location
    Seattle
    Posts
    1,313
    Quote Originally Posted by Luke101
    So, ORDER BY carries little overhead even if I have over 5000 rows? Right now, ORDER BY is very fast but I expect the database to get bigger in the future.
    5000 rows is NOTHING.

    if you put an index on the column you want to order by, you'll be fine.

    also, nobody said that your PK needs to be clustered. If you are that concerned about perf of order by, make the PK nonclustered, and the index on your sorting column clustered, as rudra suggests.

    EDIT: generally, I have found that very often it's wrong to make assumptions about where perf problems are. The only to know for sure is to measure. For sql this means use profiler and showplan. For compiled code it means profile it using some other tool. The point is you need to measure before making statements about where the perf issues lie.
    Last edited by jezemine; 10-29-06 at 12:30.

Posting Permissions

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