Обсуждение: BUG #1436: not null condition is not respected

The following bug has been logged online:

Bug reference:      1436
Logged by:          Florin Borsa
Email address:      fborsa@saratoga.ro
PostgreSQL version: 8.0
Operating system:   Windows XP
Description:        not null condition is not  respected
Details:

I ceate a table with some integer fields in this way:

create table homes ( nr int not null, name char(4))

I can insert in this table zero and null values for nr field.

where is the error, because I do not want to permit insertion of zero or
null values for nr field in homes table ?

Thank you !

On Sun, Jan 23, 2005 at 05:39:05PM +0000, Florin Borsa wrote:

> I ceate a table with some integer fields in this way:
>
> create table homes ( nr int not null, name char(4))
>
> I can insert in this table zero and null values for nr field.

Zero isn't null, so a NOT NULL constraint wouldn't prohibit it as
a value.  If you don't want to allow zero then use a CHECK constraint
(or a domain with a CHECK constraint):

CREATE TABLE homes (
    nr    integer NOT NULL CHECK (nr <> 0),
    name  char(4)
);

INSERT INTO homes (name) VALUES ('test');
ERROR:  null value in column "nr" violates not-null constraint

INSERT INTO homes (nr, name) VALUES (NULL, 'test');
ERROR:  null value in column "nr" violates not-null constraint

INSERT INTO homes (nr, name) VALUES (0, 'test');
ERROR:  new row for relation "homes" violates check constraint "homes_nr_check"

INSERT INTO homes (nr, name) VALUES (1, 'test');
INSERT 0 1

Can you provide a test case demonstrating the insertion of NULL
into a column with a NOT NULL constraint?

--
Michael Fuhr
http://www.fuhr.org/~mfuhr/