How do I update part of a composite primary key?

Question

I have a drupal site which uses CCK content fields to store a majority of its data.

I want to manually change some of the data to point to a different node version.

UPDATE content_field_table SET vid = '1234' WHERE nid = '12' AND vid = '123';

The problem is that content_field_table has a composite primary key of

PRIMARY KEY (`vid`,`delta`)

So that when I run the update statement, I get the following error:

Error Code: 1062 Duplicate entry '52979-0' for key 'PRIMARY'

How can I update the vid as needed?

Answer 1

A Primary Key must be unique. So you cannot change one record to have the same PK as an existing record

For example if you get any records from the SQL below then the update you want to perform cannot be done. You'll either need to make some other change first or delete a record or remove the PK

SELECT SOURCE.delta, 
       SOURCE.vid, 
       TARGET.delta, 
       TARGET.vid 
FROM   content_field_table SOURCE 
       INNER JOIN content_field_table TARGET 
         ON SOURCE.delta = TARGET.delta 
WHERE  SOURCE.vid = '123' 
       AND TARGET.vid = '1234' 

Answer 2

Don't change your primary keys. Also don't use compound primary keys whenever possible. MySQL (and other databases) store the records in pages in PK order. MySQL will fill each page 15/16 full before allocating space for a new page, and inserting more data there.

The problem with compound PKs or change your PK is fragmentation. I don't mean disk fragmentation I mean index fragmentation. As the PK moves, the data must be shuffled around disk to keep it in the correct order. Depending on the data set MySQL may have to move many physical rows to do this.

The same goes for updating your PK. Changing the PK changes the order on disk and requires many rows to be moved. If possible use an auto incrementing PK, or use no PK and let MySQL create an internal PK for you.

What you want is for new rows to be appending to the last free page on disk which is quick and cheap.