Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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;
share|improve this question

migrated from stackoverflow.com Jan 27 '12 at 17:07

This question came from our site for professional and enthusiast programmers.

1  
You should never change the primary key, for no reason, especially via UPDATE. Not only is it going to crash your data integrity, InnoDB will try to physically reorder records resulting in slower inserting performance. –  N.B. Jan 27 '12 at 16:51
    
How is integrity compromised if one updates the primary key? What are cascading updates for? –  ypercube Jan 27 '12 at 20:56

1 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?

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.