Re: Adding not null check constaint to list of columns

plu 12 <plutard12(at)hotmail(dot)com> schrieb:

> I have a table that contains four fields that need to be either all null or
> none null.
>
> I can add a check like so:
>
> CHECK (
> (col1 IS NULL AND col2 IS NULL AND col3 IS NULL and col4 IS NULL)
> OR
> NOT (col1 IS NULL OR col2 IS NULL OR col3 IS NULL OR col4 IS NULL)
> )
>
> But is there a simpler way to declare that? I looked at coalesce() but that
> seems to require that all the columns have the same type.

My solution:

create table c (
c1 int,
c2 int,
c3 int,
c4 int
check(
(
case when c1 is null then 0 else 1 end +
case when c2 is null then 0 else 1 end +
case when c3 is null then 0 else 1 end +
case when c4 is null then 0 else 1 end
) in(0,4)
)
);

But is this really simpler?

Andreas
--
Really, I'm not out to destroy Microsoft. That will just be a completely
unintentional side effect. (Linus Torvalds)
"If I was god, I would recompile penguin with --enable-fly." (unknown)
Kaufbach, Saxony, Germany, Europe. N 51.05082°, E 13.56889°