Add constraint to Postgres Table based on value

Question

I have the following Postgres Table

column_a | column_b  | 
---------+-----------+
 001     | 1         | 
 004     | 2         | 
 006     | 3         | 
 007     | 4         | 
 008     | 5         | 
 104     | 0         | 
 204     | 0         |

I want to add a constraint to this table such that whenever (RIGHT(column_a, 1) = '4' OR column_a = '006') AND column_a <> '004' the value of column_b needs to be equal to 0. If that's not the case, I want to throw an error.

How can I do this?

Thanks!

You should explain your constraint. Because your OR AND are contradictory — Juan Carlos Oropeza
– Juan Carlos Oropeza, Commented May 18, 2016 at 21:38
Yes 006 | 3 is an error. Basically I want all values of column_b to be 0 when the right-most digit of column_a is 0. The exceptions to this are when column_a = '004' then it does not have to be the case that column_b is 0. Also when column_a = '006' then I need it to be 0 — Vincent
– Vincent, Commented May 18, 2016 at 21:47

mu is too short · Accepted Answer · 2016-05-18 22:22:29Z

You pretty much have the answer already you just need to rearrange your logic to an expression that you can throw in a CHECK constraint.

You have a statement of the form P -> Q where -> is logical implication. But from elementary logic we know that:

(P -> Q) <-> (-P | Q)

Applying that to your implication and translating to SQL we get the SQL expression:

NOT ((RIGHT(column_a, 1) = '4' OR column_a = '006') AND column_a <> '004') OR (column_b = 0)

Then wrap it up in a CHECK:

alter table your_table
add constraint whatever_you_want_to_call_it
check (
    not ((right(column_a, 1) = '4' or column_a = '006') and column_a <> '004')
    or (column_b = 0)
)

You could use De Morgan's laws to move the not inside first parenthesized expression if you wanted or you could leave it outside to highlight the -P | Q pattern so that people would (maybe, hopefully, ...) recognize the implication in disguise.

Collectives™ on Stack Overflow

Add constraint to Postgres Table based on value

1 Answer 1

Comments

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Related