Allow SQL column to only be updated if NULL



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



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




Suggested Topics

  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2