Thread: two not null columns
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) regards garry
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