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