NOT NULL constraint over a set of columns

@Igor is quite right and a couple of OR'ed expression are fast and simple.

For a long list of columns (a, b, c, d, e, f, g in the example), this is shorter and just as fast:

CHECK (NOT (a,b,c,d,e,f,g) IS NULL)

db<>fiddle here
Old SQL Fiddle.

How does it work?

A more verbose form of the above would be:

CHECK (NOT ROW(a,b,c,d,e,f,g) IS NULL)

ROW is redundant syntax here.

Testing a ROW expression with IS NULL only reports TRUE if every single column is NULL - which happens to be exactly what we want to exclude.

It's not possible to simply reverse this expression with (a,b,c,d,e,f,g) IS NOT NULL, because that would test that every single column IS NOT NULL. Instead, negate the whole expression with NOT. Voilá.

More details in the manual here and here.

An expression of the form:

CHECK (COALESCE(a,b,c,d,e,f,g) IS NOT NULL)

would achieve the same, less elegantly and with a major restriction: only works for columns of matching data type, while the check on a ROW expression works with any columns.


You can use CHECK constraint for this. Something like:

CHECK (email is not null OR phone is not null)

Details on constraints can be found here