View Full Version : Changing the value of a value inside a primary key column.

03-13-2010, 02:36 AM
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.

Old Pedant
03-13-2010, 03:11 AM
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.