Bug #6480 Insert allowed with empty PrimaryKey Value

• Submitted: 6 Nov 2004 20:58
• Modified: 6 Nov 2004 21:40
• Reporter: Ed
• Status: Not a Bug
• Category: MySQL Server
• Severity: S1 (Critical)
• Version: 4.0.21nt
• OS: Windows (Windows XP)
• CPU Architecture: Any

[6 Nov 2004 20:58] Ed

Description:
I noticed that is was possible to insert a record in an Primary Key field with
no value or nullstring value from php as well as from a client-tool.

How to repeat:
CREATE TABLE `myTable` (
  `ID` varchar(15) NOT NULL default '',
  `Value` varchar(100) default NULL,
  PRIMARY KEY  (`ID`)
) TYPE=MyISAM

INSERT INTO myTable (ID,Value) VALUES ('','something');

[6 Nov 2004 21:40] MySQL Verification Team

The empty value here is a valid Primary Key value, the below
sequence of commands explains better:

c:\mysql\bin>mysql -uroot test
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2 to server version: 4.0.22-nt-log

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

mysql> CREATE TABLE `myTable` (
    ->   `ID` varchar(15) NOT NULL default '',
    ->   `Value` varchar(100) default NULL,
    ->   PRIMARY KEY  (`ID`)
    -> ) TYPE=MyISAM;
Query OK, 0 rows affected (0.10 sec)

mysql> INSERT INTO myTable (ID,Value) VALUES ('','something');
Query OK, 1 row affected (0.02 sec)

mysql> INSERT INTO myTable (ID,Value) VALUES ('','something');
ERROR 1062: Duplicate entry '' for key 1
mysql> INSERT INTO myTable (ID,Value) VALUES (NULL,'something');
ERROR 1048: Column 'ID' cannot be null
mysql>