Allow SQL column to only be updated if NULL

All we need is an easy explanation of the problem, so here it is.

tldr: Is it possible to create a constraint that enforces: only allow update to column if its current value is NULL.

I’m using Postgresql to update a column from NULL to a foreign key when the user takes an action.

Ideally, it works like this:

  1. User does action.
  2. If column is NULL, do a bunch of stuff and update that column to a new foreign key. Otherwise, skip to 3.
  3. Use the foreign key from that column to do something.

However, it’s possible for two users to take that action at the same time. In this case, step 2 will happen twice, since for both users at the beginning of the action the column will have still been null. Then, the foreign key set by the slightly-earlier user will be lost, along with anything that depended on it.

How can I ensure that step 2 only ever happens once? Is it possible to create a constraint that only allows an update to this column if its current value is null?
Or, at the very end of the transaction should I just check if the column has already been set, then handle it at the server level?

How to solve :

I know you bored from this bug, So we are here to help you! Take a deep breath and look at the explanation of your problem. We have many solutions to this problem, But we recommend you to use the first method because it is tested & true method that will 100% work for you.

Method 1

You could create an AFTER UPDATE trigger that throws an exception, but there are better ways:

  1. Use SELECT ... FOR NO KEY UPDATE when you read the row to prevent concurrent modifications (pessimistic locking).

  2. Use the REPEATABLE READ isolation level, then you will receive a serialization error if there is a concurrent update and can repeat the transaction (optimistic “locking”).

Method 2

You might want to check out this question and this one.

It sounds like business logic in the database. Also what will you do when the column gets updated from a value (let’s say 2) back to NULL? And what if the user then changes it again to a different value (let’s say 3)?

Note: Use and implement method 1 because this method fully tested our system.
Thank you 🙂

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0

Leave a Reply