mySQL UPDATE while ignoring table constraints

Question

I need to update a row in a mySQL database, but it is not possible to do whilst a constraint exists.

The constraint requires a combination of two columns to be unique.

As an example, firstname and lastname fields need to be unique. A SHOW CREATE TABLE users might contain something like this:

UNIQUE KEY `uniquePortTermCode` (`firstname`,`lastname`)

When I try to update, I am violating this key, so I get the error:

SQLSTATE[23000]: Integrity constraint violation: 1062 Duplicate entry 'Bob-Smith' for key 'uniqueFirstnameLastname'

Lets say I need to swap the names of two users with the same last name. So I want to set Bob Smith to Jim Smith and I want to set Jim Smith to Bob Smith. When I try the first update statement I will run into the unique key error.

I assumed that a workaround was to SET foreign_key_checks=0, but as the variable indicates this is just for foreign keys, not unique keys, and so I still get the error.

I also see that there is a variable in mySQL unique_checks, but setting this to false still doesn't solve my problem and I still get the error.

Is there a way to ignore the unique key whilst this action is performed without having change the data around like a sliding puzzle?

Answer 1

If you want to swap "Bob Smith" and "Jim Smith", you can do it with three updates:

update table t
    set firstname = 'In the process of swapping names from Bob to Jim'
    where firstname = 'Bob' and lastname = 'Smith';

update table t
    set firstname = 'Bob'
    where firstname = 'Jim' and lastname = 'Smith';

update table t
    set firstname = 'Jim'
    where firstname = 'In the process of swapping names from Bob to Jim' and lastname = 'Smith';

I would suggest wrapping these in a single transaction so they are logically executed as a single block and to put this in a stored procedure.

The alternative is to remove or disable checks on the table. This has repercussions -- in a multi-user environment, mistakes might happen on other threads. Or, the system might go down and come back up with the checking turned off.

The worst that can happen in this approach is a record gets a very awkward first name, which can be readily fixed. (And that shouldn't happen if transactions are working.)