How to implement column value change constraints in Postgres

Question

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?

Community · Accepted Answer · 2017-05-23 10:26:16Z

2

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:

PLPGSQL Cascading Triggers?

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

edited May 23, 2017 at 10:26

CommunityBot

11 silver badge

answered Jul 30, 2014 at 13:51

Erwin Brandstetter

668k159 gold badges1.2k silver badges1.3k bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Najiva · Accepted Answer · 2020-10-21 18:52:13Z

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.

Collectives™ on Stack Overflow

How to implement column value change constraints in Postgres

2 Answers 2

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related