Postgresql – NOT NULL if value is true in another column

constraintpostgresql

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?

Best Answer

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 false and number to be null at the same time"). Pick whatever is more readable to you.

Related Question