updating mysql primary keys

Question

I need to be able to swap values that are used in a primary key. Meaning, in one update statement, I need to change id 1 to id 2, and id 2 to id 1.

In other relational databases, (Sql Server and Oracle), it seems that the transaction that does the update applies the Primary key contraint after checking the values if update succeeded. This seems to be in line with what a transaction should do before it commits the change. In mySql, I get a duplicate entry error code.

Am I missing something? Is there a way around this? I'm using ver 5.1.60, InnoDB

CREATE TABLE testUpdateKeys (
 optId INT PRIMARY KEY
 ,_rowId INT
);

INSERT INTO testUpdateKeys SELECT 1,1; 
INSERT INTO testUpdateKeys SELECT 2,2;
INSERT INTO testUpdateKeys SELECT 3,3;

SELECT * FROM testUpdateKeys;

-- this should fail 
UPDATE testUpdateKeys
 SET optId = 2
WHERE _rowId = 1;


CREATE TABLE testUpdateKeys_oper ( _oper CHAR(1), _rowId INT, optId INT);

INSERT INTO testUpdateKeys_oper SELECT 'U', 2, 1;
INSERT INTO testUpdateKeys_oper SELECT 'U', 1, 2;

SELECT * FROM testUpdateKeys_oper;

-- This works in SQL Server and Oracle
UPDATE testUpdateKeys a, testUpdateKeys_oper b
 SET a.optId = b.optId
WHERE
 a._rowid = b._rowId
AND b._oper = 'U';

SELECT * FROM testUpdateKeys;

DROP TABLE testUpdateKeys;
DROP TABLE testUpdateKeys_oper;

Answer 1

I understand what you are saying, but the reason it works in SQL Server is because it's in a single statement, not because the transaction isn't committed. In SQL Server you still don't have deferred integrity - EACH statement has to satisfy the constraints, regardless of whether there is an open transaction. If there was to be a BEGIN TRANSACTION and two UPDATEs it would fail on SQL Server.

Having said all that, in your case, the UPDATE is done in a single statement, and I think you're running into a MySQL defect.

Also: MySQL giving duplicate Entry error when trying to increment date field?