Allow SQL column to only be updated if NULL
carriann last edited by
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:
- User does action.
- If column is NULL, do a bunch of stuff and update that column to a new foreign key. Otherwise, skip to 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?
You might want to check out https://stackoverflow.com/questions/16694957/rollback-transaction-on-trigger-error and https://dba.stackexchange.com/questions/158407/check-constraint-on-nullable-data-how-to-approach .
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)?