I'm wondering if it's possible to evaluate some conditions in a CHECK CONSTRAINT first and then do an OR with another expression.
My situation:
4 Tables:\
I would want to make a check constraint which basically follows the following logic:
if main.type = 0:
main.a != NULL
main.b = NULL
main.c = NULL
elif main.type = 2:
main.a = NULL
main.b = NULL
main.c != NULL
if the type = 1 I don't care what is filled in.
I tried doing a CHECK CONSTRAINT like so:
CHECK((main.type = 0 AND main.a != NULL AND main.b = NULL AND main.c = NULL) OR
(main.type = 2 AND main.a = NULL AND main.b = NULL AND main.c != NULL))
The problem is that the inner parentheses get deleted/ ignored after applying this constraint which means it becomes mostly nonsense and it doesn't work.
Is this possible to solve with a CHECK CONSTRAINT or should a trigger be used for this?
In PostgreSQL, the Check constraint can be defined by a separate name. It is used to control the value of columns being inserted. It allows us to verify a condition that the value being stored into a record. If the statement is false, then the data disrupts the constraint which is not saved in the table.
The CHECK constraint is used to limit the value range that can be placed in a column. If you define a CHECK constraint on a column it will allow only certain values for this column.
Currently DROP CONSTRAINT drops only CHECK constraints. To remove a PRIMARY or UNIQUE constraint, drop the relevant index using the DROP INDEX command. To remove FOREIGN KEY constraints you need to recreate and reload the table, using other parameters to the CREATE TABLE command.
!= NULL
! That is not going to work. This seems to be the logic you want:
CHECK ( (type = 0 and a is not null and b is null and c is null) or
(type = 2 and a is null and b is null and c is not null) or
(type = 1)
)
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With