MySQL Bugs: #390: Discrepancy with ISO about primary key and NOT NULL (error 1171)

Bug #390	Discrepancy with ISO about primary key and NOT NULL (error 1171)
Submitted:	7 May 2003 9:14	Modified:	13 May 2003 11:10
Reporter:	Raf Schietekat	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server	Severity:	S3 (Non-critical)
Version:	4.0.13	OS:	Any (All)
Assigned to:	Per-Erik Martin	CPU Architecture:	Any

Description:
manual.pdf as downloaded on 2003-05-02 says that (6.5.3 CREATE TABLE
Syntax, p. 505):
"A PRIMARY KEY is a unique KEY with the extra constraint that all key
columns must be defined as NOT NULL. In MySQL the key is named PRIMARY.
A table can have only one PRIMARY KEY. If you don?t have a PRIMARY KEY
and some applications ask for the PRIMARY KEY in your tables, MySQL will
return the first UNIQUE key, which doesn?t have any NULL columns, as the
PRIMARY KEY."

and mysql-4.0.12-win.zip indeed gives this error if NOT NULL is not used
in a table definition:
"ERROR 1171: All parts of a PRIMARY KEY must be NOT NULL;  If you need
NULL in a key, use UNIQUE instead"

but "ISO/IEC 9075-1:1999 (E)" says in "4.6.6.3 Table constraints":
"A primary key constraint is a unique constraint that specifies PRIMARY
KEY. A primary key constraint is satisfied if and only if no two rows in
a table have the same non-null values in the unique columns and none of
the values in the specified column or columns are the null value."

The difference is "all key columns must be defined as NOT NULL" (MySQL)
vs. (with my *emphasis*) "none of the *values* in the specified column
or columns are the null value" (ISO, whose somewhat strange wording
exactly reflects the difference between unique and primary key). ISO
basically says that the primary key constraint *implies* the NOT NULL
constraint on all participating columns, whereas MySQL *requires* it,
erroneously, in my opinion. (And also in Oracle's opinion, to name an
obscure reference, but the main thing is of course the text of the
standard.)

How to repeat:
mysql> create table test(
    -> mykey varchar(2),
    -> primary key(mykey));
ERROR 1171: All parts of a PRIMARY KEY must be NOT NULL;  If you need NULL in a
key, use UNIQUE instead
mysql>

Suggested fix:
Workaround: explicitly add NOT NULL to port to MySQL.
Solution: remove error 1171 (perhaps make it a warning so that the SQL
can still be used with previous versions), change the documentation
(also describing the change).

Or else give a pretty convincing argument for why MySQL deviates from
the ISO standard. Have I missed any relevant clarifications or addenda
or so?

This also fixed this problem (as noted in t/key.test). The ALTER TABLE
below now works:

CREATE TABLE t1 (program enum('signup','unique','sliding') not null,  type enum('basic','sliding','signup'),  sites set('mt'),  PRIMARY KEY (program));

ALTER TABLE t1 modify program enum('signup','unique','sliding');

Thank you for your bug report. This issue has been fixed in the latest
development tree for that product. You can find more information about
accessing our development trees at 
    http://www.mysql.com/doc/en/Installing_source_tree.html

An additional note about what ISO's requirements are ... In SQL-92 entry 
level, any column which you use in a PRIMARY KEY or UNIQUE clause must be 
explicitly declared as NOT NULL. So many DBMSs (including DB2 and Ingres and 
InterBase and Sybase the last time I looked) require NOT NULL. In SQL-92 
intermediate, and in SQL-99, the NOT NULL is implied. MySQL is now moving to 
SQL-99 compliance, hence the change, but this example shows why it's good to 
be specific about the ISO version and level number when asking about 
compliance.

It's still not fixed! We have to specify it explicitly. I am using 4.1