Detta har bitit mig och andra, postas här för kännedom.
Tabell och data
CREATE TABLE test (
title CHAR(1) NOT NULL PRIMARY KEY,
sortkey INTEGER NOT NULL UNIQUE
);
INSERT INTO test VALUES ( 'A', 1 );
INSERT INTO test VALUES ( 'B', 2 );
INSERT INTO test VALUES ( 'C', 3 );
INSERT INTO test VALUES ( 'D', 4 );
Problem
Detta statement borde/skall fungera:
UPDATE test
SET sortkey = sortkey + 1
Du kommer få detta felmeddelandet: Duplicate entry '2' for key '2'
Workaround
Ibland är det möjligt att använda denna workaround.
UPDATE test
SET sortkey = sortkey + 1
ORDER BY sortkey DESC
UPDATE test
SET sortkey = sortkey - 1
ORDER BY sortkey ASC
Varför?
Mark Matthews @ MySQL skrev:
Because MyISAM tables are not transactional, MySQL _can't_ defer
constraint checking to end-of-statement, because if a constraint was
violated during the processing of the statement, there would be nothing
to roll-back to. Therefore, MySQL has to perform constraint checks at
row-update time. This behavior is the same across all table types,
transactional or not, for consitencies' sake.
Marks inlägg
http://groups.google.se/groups?selm=bbqkjc%2412fq%241%40FreeBSD.csie.NCTU.edu.tw
Hela tråden:
http://groups.google.se/groups?threadm=bbop7d%242s4l%241%40FreeBSD.csie.NCTU.edu.tw
Hmm, i konsekvensens namn... Undra vad konsekvensen borde vara!
Nu vet ni IAF :)