Bug#390173: Someone needs to try to reproduce this

[Keith Lawson]

On Tue, 26 Apr 2011 18:22:25 +0200, Ansgar Burchardt wrote:
> Hi,
>
> Keith Lawson writes:
>
>> MySQL won't even let me create the situation described. While it 
>> will
>> allow for NULL or 0 in a primary key field it only allows for one of
>> them:
>
> [...]
>
>> mysql> insert into test_table (pkey,text) values ('null','test no
>> key'); ERROR 1062 (23000): Duplicate entry '0' for key 'PRIMARY'
>
> It's interesting that MySQL believes the string 'null' to be an 
> integer,
> but I suppose you should try without the quotes.
>

That fails too:

mysql> insert into test_table (pkey,text) values (null,'testing null 
key');
ERROR 1048 (23000): Column 'pkey' cannot be null

Which seems to agree with the documented functionality on the MySQL 
site[1].

I didn't specify "not null" for the "pkey" field when I created the 
table but here's what it looks like now:

mysql> show create table test_table;
+------------+----------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table      | Create Table                                             
                                                                         
                       |
+------------+----------------------------------------------------------------------------------------------------------------------------------------------------------+
| test_table | CREATE TABLE `test_table` (
   `pkey` int(11) NOT NULL,
   `text` varchar(100) DEFAULT NULL,
   PRIMARY KEY (`pkey`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 |
+------------+----------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.02 sec)


Links:
------
[1] http://dev.mysql.com/doc/refman/5.0/en/create-table.html