Maintaining Invariant Constraints in PostgreSQL using Trigger Functions
Recently a feature I was working on required me to alter a unique constraint which existed upon a table column.
The invariant had now been weakened to allow storing of duplicate
link_id (excluding NULL).
Sadly the ease in which I was able to add the general unique constraint had disappear.
However, I was able to take advantage of insertion/update triggers to again provide me with these invariant reassurances.
I should note that I’m in favor of having business critical constraints placed within the database layer, even if this seems like a blurring of responsibility between application logic and the data-store.
As you can see within PostgreSQL we are able to write extremely clear trigger functions which can be used to alter actions that occur in the database. We hope that the application layer will safe-guard us from an attempt to break this invariant, but as this is a critical area of our domain we can provide another level of validation just to make sure.