CODEWITHSUNDEEP
sqlpostgresqlconstraints
Oben Sonne
Oben Sonne

Reputation: 9953

How to implement column value change constraints in Postgres

I'd like to ensure that the value of a column may change in a defined way only. Examples are columns containing a state value which must only change according to defined state transitions) or columns which may be set from NULL to something only once.

I'd like to avoid implementing these checks on the application layer.

I could not find a solution using regular constraints. Is there another approach to accomplish that or are constraints like this intentionally not supported on the database layer?

Upvotes: 1

Views: 842

Answers (2)

Najiva
Najiva

Reputation: 162

I wrote a python script that generates a CHECK constraints based on a desired restrictions. These constraints enforce setting of null columns for each state.

E.g. When I have "Orders" table and an order can have states "Paid" or "Not paid" and lets say there is a column "Invoice_number" of type text. If you would want to update any order to the "Paid" state, the check constraint would not allow that, unless you set "Invoice_number" from NULL to some non-NULL value.

Simple CHECK could look like this:

ALTER TABLE orders ADD CONSTRAINT orders_state_paid_check CHECK ( CASE WHEN state = 'paid' AND (invoice_number is null) THEN false ELSE true END);

You can check the tool here: https://github.com/Najiva/check_gen

Right now it is working with postgres > 9.4.

Upvotes: 0

Erwin Brandstetter
Erwin Brandstetter

Reputation: 659247

Constraints can only enforce rules based on current values of a row (check constraint)
- or referential integrity between current states of related rows in different tables (foreign key constraint)
- or to a limited extent on current values of all rows in a table (exclusion constraint).

You want to enforce constraints between previous state and current state. That's typically implemented with a trigger.
Example:

There are many example here on SO. Try a search.

Upvotes: 2

Related Questions