Back

MySQL deferred constraints and unique checks

Captain's log, stardate d227.y40/AB

Databases PostgreSQL MySQL
Xavier Redó
Founder & 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:

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.

Share this post

Related Articles

Vortex

Introduction to Kubernetes

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

Read full article
Robocleaner

Remember to vacuum your databases!

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

Read full article
Mesh

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.

Read full article