Update unique or primary keys in MySQL

Question

I'm building a database management tool for a client to use on his own, and I'm having some problem dealing with the possibility of the update of primary/unique keys. So, given that the data for the update is passed by a PHP script on a per row basis, here's what I've come up with (from "immediatly" to "after some time"):

1. DELETE/INSERT instead of UPDATE (awful, I now...):

   DELETE FROM table WHERE unique_key=x;
   DELETE FROM table WHERE unique_key=y;
   INSERT INTO table VALUES (unique_key=y, field=record1), (unique_key=x, field=record2);
   

2. Alter my primary/unique key and then substitute them with the modified value:

   UPDATE table SET unique_key=x* WHERE unique_key=x;
   UPDATE table SET unique_key=y* WHERE unique_key=y;
   UPDATE table SET unique_key=y WHERE unique_key=x*;
   UPDATE table SET unique_key=x WHERE unique_key=y*;
   

3. Add a not modifiable auto_increment field "id" to all my tables, which act as a surrogate primary key

As now, I'm on the route of adding an "id" field to everything. Other options?

Answer 1

Updating a primary key isn't a problem; all values in SQL (and in the relational model) are supposed to be updatable.

The problem seems to be swapping primary keys, which

• doesn't make sense to me if you use surrogate keys (because they're meaningless, so updates aren't necessary) and which
• doesn't make sense to me if you use natural keys, because that's like swapping my StackOverflow userid with yours.

Adding an "ID" column to every table won't help you. The "unique_key" column still has to be declared unique. Adding an "ID" column doesn't change that business requirement.

You could swap primary key values if MySQL supported deferred constraints. (Deferred constraints are a feature in standard SQL.) But MySQL doesn't support that feature. In PostgreSQL, for example, you could do this.

create table test (
  unique_key char(1) primary key deferrable initially immediate,
  other_column varchar(15) not null
);

insert into test values 
('x', 'record2'),
('y', 'record1');

begin;
set constraints test_pkey deferred;
update test set unique_key = 'y' where other_column = 'record2';
update test set unique_key = 'x' where other_column = 'record1';
commit;

select * from test;

unique_key  other_column
--
y           record2
x           record1