Re: Adding not null check constaint to list of columns

plu 12 <plutard12@hotmail.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°