Bug #419 NOT NULL Constraint

• Submitted: 12 May 2003 8:14
• Modified: 13 May 2003 2:29
• Reporter: Giuseppe Damiani
• Status: Closed
• Category: MySQL Server
• Severity: S2 (Serious)
• CPU Architecture: Any

[12 May 2003 8:14] Giuseppe Damiani

Description:
Hello, 
the constraint not null is not respected when a recording is updated and created.

How to repeat:
create table test (
 id_test int not null primary key auto_increment,
 word    varchar(10) not null
);

Execute : insert into test (word) values (NULL); is ok
ERROR 1048 : ...

Execute : insert into test (word) values ('word'); is ok
+---------+------+
| id_test | word |
+---------+------+
¦        1¦ word ¦
+---------+------+

Execute : update test set word=NULL where id_test = 1; is no ok

+---------+------+
| id_test | word |
+---------+------+
¦        1¦      ¦
+---------+------+

Execute : alter table test add word_2 varchar(10) null; 
Execute : insert into test (word_2) values ('test'); is no ok
+---------+------+--------+
| id_test | word | word_2 |
+---------+------+--------+
¦        1¦      ¦        |
+---------+------+--------+
¦        2¦      ¦  test  |
+---------+------+--------+

Suggested fix:
Since the "word" attributed should not contain value NULL, the request update should generate an error instead of affecting the value null

[13 May 2003 2:29] Michael Widenius

Thank you for your bug report. This issue has been addressed in the
documentation. The updated documentation will appear on our website
shortly, and will be included in the next release of the relevant
product(s).

MySQL doesn't by default support NOT NULL constraint checking.
This is listed in the 'open bugs' section in the manual.
I will update the manual with an even better description of this issue.