Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 2 of 2
  1. #1
    New to the CF scene
    Join Date
    Mar 2010
    Posts
    3
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Changing the value of a value inside a primary key column.

    I'm sorry, but it was a little hard to explain in the title of the thread. However this is a more in-depth explanation:

    I have a table named Test, and in that table I have a column (which is the primary key) named "cid".

    Full Table:
    Cid Slot1 Slot2 Slot3

    I then insert a new row into the table with say these values:

    1 "Test" "Test2" "Test3"

    So currently I have a row with the values above inside of my table named Test.

    The problem is, I want to be able to change the CID (via code), and I just wanted to be reassured that it is possible to set the value of a primary key after it has already been set without going straight into the database and doing it manually.

  • #2
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    25,934
    Thanks
    79
    Thanked 4,424 Times in 4,389 Posts
    So long as it's not an AUTO_INCREMENT field *AND* so long as it is not used as the foreign key in a PK to FK relationship, yes.

    Now, *IF* you are using INNODB tables and *IF* you have specified CASCADE UPDATE for the FK relationship, then the latter of those two conditions doesn't apply: MySQL would do the update for you.

    Does that make sense?

    Aside from PK's that are participating in PK/FK relationships, the only restriction on changing a PK's value is that you don't duplicate the value in any other record. And of course AUTO_INCREMENT fields are immutable.

    Note that if you are using MyISAM tables then the DB will allow the change in the PK even if there is some FK referring to it. This is a dangerous situation and will leave "dangling Foreign Keys", so I can't recommend changing PK's in MyISAM tables unless you are 100% sure that no other table is linked to that table.
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.


  •  

    Posting Permissions

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