Allow SQL column to only be updated if NULL

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?

• user1822: Sounds like using serializable as the isolation level for the concurrent transactions would solve this.

Answer 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").

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

• stonefree: In this case, the value will never be set back to null. You can imagine the column is being "upgraded" to a value, and it will then forever have that value.
• stonefree: Also, you’re right that this is business logic in the db, but I’m prioritizing reliability (and simplicity of implementation and speed) over purity.
• DrTrunks Bell: Then a trigger and a function will do the trick