two not null columns

Garry Saddington <garry@schoolteachers.co.uk> schrieb:

How would I write a table check constraint to make sure that two columns don't
both have nulls at the same time.
The following doesn't do it because it because it does not allow one column to
be null while the other holds data?

check(teachgroup is not null AND set is not null)

test=# create table not_null (a int, b int, check((a is not null) or (b is not null)));
CREATE TABLE
test=*# insert into not_null values (1,1);
INSERT 0 1
test=*# insert into not_null values (1,NULL);
INSERT 0 1
test=*# insert into not_null values (null,2);
INSERT 0 1
test=*# insert into not_null values (null,null);
ERROR: new row for relation "not_null" violates check constraint "not_null_check"

Regards, 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." (unknow)
Kaufbach, Saxony, Germany, Europe. N 51.05082�, E 13.56889�

On Monday 14 April 2008 17:44, Andreas Kretschmer wrote:

Garry Saddington <garry@schoolteachers.co.uk> schrieb:

How would I write a table check constraint to make sure that two columns
don't both have nulls at the same time.
The following doesn't do it because it because it does not allow one
column to be null while the other holds data?

check(teachgroup is not null AND set is not null)

test=# create table not_null (a int, b int, check((a is not null) or (b is
not null))); CREATE TABLE
test=*# insert into not_null values (1,1);
INSERT 0 1
test=*# insert into not_null values (1,NULL);
INSERT 0 1
test=*# insert into not_null values (null,2);
INSERT 0 1
test=*# insert into not_null values (null,null);
ERROR: new row for relation "not_null" violates check constraint
"not_null_check"

Works great, thanks
regards
garry