5

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?

12

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.

  • Thanks a lot for the example and explanation, that is really helpful! This worked just like i wanted. I must got my logic wrong at first – geogrow Aug 1 '16 at 11:26
  • 1
    Yes, your constraint was more restrictive. It forced the attribute to be never false and number to be always not null. By the way, both your and my code allow the attribute to be unknown (null). Do you want that to happen? – ypercubeᵀᴹ Aug 1 '16 at 11:30
  • Oh yes thanks, I can see how the logic work in the check constraint now =). In my design attribute is allowed to be null. But I suppose it would be better to set default as 'false' and not allowing NULL – geogrow Aug 1 '16 at 11:36
  • Yes. As it is now, with null or false attribute, there is no restriction for the number (both null and not null would be allowed for number). – ypercubeᵀᴹ Aug 1 '16 at 11:38

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.