MySQL deferred constraints and unique checks

Captain's log, stardate d227.y40/AB

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

Hard disk - Photo by Jandira Sonnendeck

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.

Xavier Redó

Xavier Redó

Responsible for architecture and development. He's the one to blame for our techstack (Ruby on Rails, Angular, React, Node.js) and the one to be thanked as well. One of the very few developers with social skills we know.

comments powered by Disqus

You're one step away from meeting your best partner in business.

Hire Us