Allow SQL column to only be updated if NULL

by

The question:

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?

The Solutions:

Below are the methods you can try. The first solution is probably the best. Try others if the first one doesn’t work. Senior developers aren’t just copying/pasting – they read the methods carefully & apply them wisely to each case.

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)?


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 Comment