NOT NULL if value is true in another column

Question

I have a similar table to this one

create table my_table
(
id serial,
attribute boolean,
number integer
)

Is there a way to have it force not null in column number IF attribute value is true?

So if a record is saved with attribute value 'true' then number must be given a value.

EDIT: After some digging I have tried this

alter table my_table 
add constraint number_must_have_value CHECK (attribute = 't' and number IS NOT NULL)

It throw constraint is violated by some row, but if I run:

select * from my_table where attribute = 't' and number IS NOT NULL

I retrive 0 rows. So my data seem to be OK?

To force it anyway I try to use

constraint number_must_have_value NOT VALID CHECK (attribute = 't' and number IS NOT NULL)

But can't get the NOT VALID option to work. I get a syntax error. Is it not in the right place?

score 38 · Accepted Answer · edited Dec 10 '24 at 14:21

This is a rather simple CHECK constraint:

CREATE TABLE my_table
(
id serial,
attribute boolean,
number integer,
CONSTRAINT if_attribute_then_number_is_not_null 
   CHECK ( (NOT attribute) OR (number IS NOT NULL) ) 
) ;

The logic behind the code is that the logical restriction if a then b is written in boolean logic as (not a) or (b). May seem counter-intuitive at first look but if you write the possible boolean combinations it works out.

The constraint could also be written as CHECK ( NOT (attribute AND number IS NULL) ) which may seem a bit more explanatory ("do not allow attribute to be true and number to be null at the same time"). Pick whatever is more readable to you.

NOT NULL if value is true in another column

1 Answers1