cortilap @ freenode's #postgresql asked about how to create a check() that will allow only one of the columns to be not null.
it doesn't sound cool, let's see:
with 2 columns (a,b) you make a check: check ( (a is not null and b is null) or (a is null and b is not null) or (a is null and b is null))
whoa. and what about 3 columns? 4?
of course it creates some questions about the schema, but is there a way to do it? without such long checks?
one solution is to make a function to check it. but perhaps a simpler solution is possible?
luckily all of the fields are ints.
a quick think, and here we go:
check (coalesce(a*0, 1) + coalesce(b*0, 1) + coalesce(c*0, 1) > 1)
and what is the field was text? same thing, but instead of doing “X"*0, i would do “length(X)*0" 🙂