Composite Not Null constraint on two columns

user_9210263 0
Viewed 666

I want to have a constraint in which I make sure that at least of two columns is not null. Basically, from those two columns, one must contain values.

How can I have a constraint like that? Is it possible on liquibase? If not, is it possible via SQL or some postgres specific thing?

liquibase postgresql sql
2 Answers

I like using num_nonnulls() for this:

For at least one not null column:

check (num_nonnulls(col1, col2) >= 1)

For exactly one not null column:

check (num_nonnulls(col1, col2) = 1)

Liquibase has not built-in change for check constraints (at least not in the community version), so you will need a <sql> change for this:

<sql> 
  alter table the_table
    add constraint at_least_one_not_null
    check (num_nonnulls(col1, col2) >= 1)   
</sql>
user_3303150

You can use a check constraint. For at least one non-NULL value:

check (col1 is not null or col2 is not null)

If you need for exactly one to contain values:

check (col1 is not null and col2 is null or
       col1 is null and col2 is not null
      )

Or in Postgres:

check ( (col1 is not null)::int + (col2 is not null)::int = 1 )
user_11440350
Related