Create a constraint based on column value to ensure not null

Question

I have a table with 4 columns, let's say: col_1, col_2, col_3, col_4, where col_1 is of type TEXT and the others are INTEGER.

I want to add a constraint on col_1 that goes as follow:

if (col_1 = 'a') then col_2 cannot be NULL
if (col_1 = 'b') then col_2 AND col_3 cannot be NULL
if (col_1 = 'c') then col_4 cannot be NULL

I tried to use a CHECK as follows:

ALTER TABLE "my_table" 
    ADD CONSTRAINT check_for_type
        CHECK ((col_1 = 'a' AND 'col_2' IS NOT NULL)
               OR (col_1 = 'b' AND ('col_2' IS NOT NULL AND 'col_3' IS NOT NULL))
               OR (col_1 = 'c' AND 'col_4' IS NOT NULL)
              );

But it is still allowing me to add a row with col_1 = 'a' and col_2 = NULL

I'd be grateful for any help.

Thanks in advance

Answer 1

'col_2' IS NOT NULL

will always be true since it is comparing a hardcoded string of the text 'col_2' against null. It is not comparing the value in that column with null.

To refer to the value in the column, you will want to use double-quotes or no quotes.

What you are looking for is likely:

ALTER TABLE "my_table" ADD CONSTRAINT check_for_type
CHECK (
  (col_1 = 'a' AND col_2 IS NOT NULL)
  OR (col_1 = 'b' AND (col_2 IS NOT NULL AND col_3 IS NOT NULL))
  OR (col_1 = 'c' AND col_4 IS NOT NULL)
);

This is just your original constraint treating col_2/col_3/col_4 as column names instead of hardcoded strings.