CODEWITHSUNDEEP
sqlpostgresql
Vincent
Vincent

Reputation: 8796

Add constraint to Postgres Table based on value

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!

Upvotes: 0

Views: 48

Answers (1)

mu is too short
mu is too short

Reputation: 434615

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.

Upvotes: 1

Related Questions