Thread: Adding not null check constaint to list of columns

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.

---

Windows Live™ Hotmail®: Chat. Store. Share. Do more with mail. See how it works.

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°