MySQL Bugs: #66987: Missing error message / warning when trying to create a primary key with NULL va

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

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

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

Thank you for the report.

Verified as described: warning for such CREATE TABLE statements would be nice.

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.