Description:
In most DBMSs, check violations are checked at the end of a statement by default. And they are optionally deferrable at transaction commit.
MySQL validates constraints after modifying each row. A couple of comments:
* In some cases, this forces us to add an ORDER BY clause to UPDATE, which sounds weird to whoever doesn't know MySQL (and to me, to be honest). And I can even show cases where ORDER BY won't help much.
* Is there any case in which the current behaviour is desirable?
I suggest moving the validation at the end of each statement.
Note that setting unique_checks to 0 doesn't solve the problem (try it with the snippets below) and is not really expected to.
How to repeat:
mysql> create table ti (id int auto_increment primary key, a int unique);
Query OK, 0 rows affected (0.12 sec)
mysql> insert into ti (a) values (3), (2), (1);
Query OK, 3 rows affected (0.03 sec)
Records: 3 Duplicates: 0 Warnings: 0
mysql> UPDATE ti SET a = id;
ERROR 1062 (23000): Duplicate entry '1' for key 'a'
