MySQL Bugs: #15542: Secondary part of PRIMARY KEY can be specified without NOT NULL

Bug #15542	Secondary part of PRIMARY KEY can be specified without NOT NULL
Submitted:	7 Dec 2005 0:16	Modified:	7 Dec 2005 14:27
Reporter:	Arjen Lentz	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server	Severity:	S3 (Non-critical)
Version:	5.0.15	OS:	Any (any)
Assigned to:		CPU Architecture:	Any

Description:
A secondary part of a composite primary key silently accepts a column that is not specified as NOT NULL. When checking the SHOW CREATE TABLE, the server has modified the column definition to be NOT NULL.

A user has noted to me that 4.0.22 also has the same behaviour.

How to repeat:
(this is a simplified construct originating from vBulletin).

CREATE TABLE deletionlog (
  primaryid INT UNSIGNED NOT NULL DEFAULT '0',
  type ENUM('post', 'thread'),
  PRIMARY KEY (primaryid, type)
);

SHOW CREATE TABLE deletionlog;
Create Table: CREATE TABLE `deletionlog` (
  `primaryid` int(10) unsigned NOT NULL default '0',
  `type` enum('post','thread') NOT NULL default 'post',
  PRIMARY KEY  (`primaryid`,`type`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1

Suggested fix:
This, IMHO, is a very undesirable silent type conversion. And error would be appropriate.
In fact, there is a MySQL error message 1171: "All parts of a PRIMARY KEY must be NOT NULL; If you need NULL in a key, use UNIQUE instead"

Verified with both 4.1 and 5.0

No Error nor even Warning

Thank you for taking the time to write to us, but this is not
a bug. Please double-check the documentation available at
http://www.mysql.com/documentation/ and the instructions on
how to report a bug at http://bugs.mysql.com/how-to-report.php

Additional info:

Right, there is an error message, and MySQL used to return this error.
Guess why it was changed ? Because SQL standard (2003, at least) specifies:

5) If the <unique specification> specifies PRIMARY KEY, then for each <column name> in the explicit or implicit <unique column list> for which NOT NULL is not specified, NOT NULL is implicit in the <column definition>.

(in the  Part 2: Foundation, 11.7 <unique constraint definition>).
Also in the manual:

   * A `PRIMARY KEY' is a unique `KEY' where all key columns must be
     defined as `NOT NULL'. If they are not explicitly declared as `NOT
     NULL', MySQL declares them so implicitly (and silently).