Making a CHECK CONSTRAINT with OR in postgres SQL

Question

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:\

• main, which an id, a type variable(lets make it a smallint of 0-2), and foreign keys to the following tables:
• a, b, and c, which only contain an id

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?

Answer 1

!= 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)
      )