|
|
 | | From: | Florin Borsa | | Subject: | BUG #1436: not null condition is not respected | | Date: | Sun, 23 Jan 2005 17:39:05 GMT |
|
|
 |
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 !
---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postgresql.org so that your
message can get through to the mailing list cleanly
|
|
| | From: | Michael Fuhr | | Subject: | Re: BUG #1436: not null condition is not respected | | Date: | Mon, 24 Jan 2005 01:22:19 -0700 |
|
|
| 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/
---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match
|
|
|
