Bug #66987 Missing error message / warning when trying to create a primary key with NULL va
Submitted: 27 Sep 2012 10:46 Modified: 14 Nov 2013 0:00
Reporter: Joffrey MICHAIE Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: DDL Severity:S3 (Non-critical)
Version:All OS:Any
Assigned to: CPU Architecture:Any
Tags: compound primary key null

[27 Sep 2012 10:46] Joffrey MICHAIE
Description:
Hi,

It is a fact that (parts of) primary key cannot be null.
When I try to create a table, with part of the primary key default null, MySQL converts it without warning.

There should be some warning/error explaining that data type was converted

How to repeat:
Tested with InnoDB and ndbcluster
Tested with default sql_mode, sql_mode=TRADITIONAL, sql_mode = STRICT_ALL_TABLES

mysql> CREATE TABLe not_null (a int unsigned auto_increment, b int default null, primary key (a,b)) engine=InnoDB;
Query OK, 0 rows affected (0.00 sec)

mysql> show create table not_null;
+----------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table    | Create Table                                                                                                                                                                 |
+----------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| not_null | CREATE TABLE `not_null` (
  `a` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `b` int(11) NOT NULL DEFAULT '0',
  PRIMARY KEY (`a`,`b`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 |
+----------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

Suggested fix:
Add error message warning of implicit conversion
mysql> INSERT INTO not_null VALUES (NULL,NULL);
ERROR 1048 (23000): Column 'b' cannot be null
[27 Sep 2012 14:36] MySQL Verification Team
I suppose this falls into a similar category of mysql accepting syntax for one thing but doing another..  e.g.
http://bugs.mysql.com/bug.php?id=47771
[6 Dec 2012 20:11] Sveta Smirnova
Thank you for the report.

Verified as described: warning for such CREATE TABLE statements would be nice.
[14 Nov 2013 0:00] Paul DuBois
Noted in 5.7.3 changelog.

Columns in a PRIMARY KEY must be NOT NULL, but primary key columns
that were defined to permit NULL were not rejected. Now such column
definitions produce an error.