Bug #41734 inserting null value in primary key field does not give any error

• Submitted: 24 Dec 2008 13:52
• Modified: 24 Dec 2008 15:32
• Reporter: Nihar Paital
• Status: Not a Bug
• Category: MySQL Server: DDL
• Severity: S3 (Non-critical)
• Version: MySQL-server-5.1.21-0.glibc23.i386.rpm
• OS: Linux
• CPU Architecture: Any

[24 Dec 2008 13:52] Nihar Paital

Description:
mysql> create table t2(f1 int,f2 int,primary key(f1));
Query OK, 0 rows affected (0.00 sec)

mysql> insert into t2 values(1,10);
Query OK, 1 row affected (0.00 sec)

mysql> insert into t2 values(3,30);
Query OK, 1 row affected (0.00 sec)

I am inserting a null value in f1 field which is a primary key field as follows
It is executed successfully. And the select query also displays the data by inserting a 0 in f1 field.

mysql> insert into t2(f2) values(30);
Query OK, 1 row affected (0.00 sec)

mysql> select * from p1;
+----+------+------+
| f1 | f2   | f3   |
+----+------+------+
|  1 |   10 | Papu | 
|  2 |   20 | Sima | 
|  0 |   30 | rana | 
+----+------+------+
3 rows in set (0.00 sec)

How to repeat:
Open mysql
run the above commands

Suggested fix:

Expectation: I think the insert statement should give error "NOT NULL constraint violation"

[24 Dec 2008 15:32] MySQL Verification Team

Thank you for the bug report.

c:\dbs>c:\dbs\5.1\bin\mysql -uroot --port=3510 --prompt="mysql 5.1 >"
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.1.31-nt-debug-log Source distribution

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql 5.1 >use db9
Database changed
mysql 5.1 >create table t2(f1 int,f2 int,primary key(f1));
Query OK, 0 rows affected (0.06 sec)

mysql 5.1 >show create table t2\G
*************************** 1. row ***************************
       Table: t2
Create Table: CREATE TABLE `t2` (
  `f1` int(11) NOT NULL DEFAULT '0',
  `f2` int(11) DEFAULT NULL,
  PRIMARY KEY (`f1`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

mysql 5.1 >insert into t2(f2) values(30); /* first one accepts the default 0 */
Query OK, 1 row affected (0.00 sec)

mysql 5.1 >insert into t2(f2) values(40); /* second don't accepts the default 0 */
ERROR 1062 (23000): Duplicate entry '0' for key 'PRIMARY'
mysql 5.1 >select * from t2;
+----+------+
| f1 | f2   |
+----+------+
|  0 |   30 |
+----+------+
1 row in set (0.00 sec)

mysql 5.1 >