MySQL deferred constraints and unique checks

MySQL and PostgreSQL are very similar but not exact. Take a look at this scenario that works with PostgreSQL but not with MySQL.

The SQL standard says that, by default, the constraints check (foreign and unique keys) should be deferred. This means that the DB server won't check those before a transaction is commited.

Unfortunately MySQL is not following this standard and this could cause problems like the following one.

I have a unique key on a position column. I don't want to have repeated keys there. And I want to change the position of 2 elements with this initial state:

id=1, position=2
id=2, position=1

... to this state:

id=1, position=1
id=2, position=2

You would do it like this:

BEGIN TRANSACTION;

UPDATE table SET position = 1 WHERE id = 1;
UPDATE table SET position = 2 WHERE id = 2;

COMMIT TRANSACTION;

This will work on PostrgreSQL but will fail in MySQL. Because MySQL checks the unique constraint inmediatelly and PostgreSQL will wait until the Transaction is commited.