Re: BUG #1436: not null condition is not respected - Mailing list pgsql-bugs

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/