MySQL deferred constraints and unique checks

Diario del capitán, fecha estelar d227.y40/AB

Databases PostgreSQL MySQL
Fundador & CTO
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:


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


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.

Compartir este post

Artículos relacionados


Introduction to Kubernetes

We've recently gotten our hands on Kubernetes, and we've compiled a quick guide on how to get started.

Leer el artículo

Remember to vacuum your databases!

There's a way to declutter PostgreSQL databases that will impact the performance of your projects. Learn about vacuum.

Leer el artículo

Query data from PostgreSQL to represent it in a time series graph

In this blog post, our CTO, Xavi, will show us how to query data from PostgreSQL to represent it in a time series graph.

Leer el artículo