BECAUSE NULL IS NOT GREATER THAN 0
NOR IS NULL LESS THAN 0
NOR IS NULL = 0
just change your constraint
check nvl(x,0) > 0
Nick Kean wrote in message ...
> Oracle7 Server Release 7.3.4.0.1 - Production
>and also on
> Oracle8 Enterprise Edition Release 8.0.5.1.0 - Production
>
>SQL> create table tmp (x integer);
>Table created.
>
>SQL> alter table tmp add constraint foo check (x > 0);
>Table altered.
>
>SQL> insert into tmp values (null);
>1 row created.
>
>SQL> select count(*) from tmp where x > 0;
> COUNT(*)
>----------
> 0
>
>SQL> select count(*) from tmp;
> COUNT(*)
>----------
> 1
>
>
> Why doesn't adding the "foo" constraint to the x column stop nulls from
being
>added? I know I could just make the column NOT NULL but shouldn't adding
that
>constraint have the same effect?
>
>
>--
>Reality is a cheap substitute for Prozac
Received on Thu Oct 21 1999 - 18:11:48 CDT
