| Bug #11465 | CREATE TABLE silently changes NULL to NOT NULL if PRIMARY KEY | ||
|---|---|---|---|
| Submitted: | 20 Jun 2005 17:01 | Modified: | 30 Jun 2007 1:45 |
| Reporter: | Jeremy Cole (Basic Quality Contributor) (OCA) | Email Updates: | |
| Status: | Won't fix | Impact on me: | |
| Category: | MySQL Server | Severity: | S3 (Non-critical) |
| Version: | OS: | Any | |
| Assigned to: | Antony Curtis | CPU Architecture: | Any |
[20 Jun 2005 18:10]
MySQL Verification Team
Thank you for the bug report.
[14 Sep 2005 9:13]
Sergei Golubchik
Just to mention that if neither NULL nor NOT NULL is specified, we have to imply NOT NULL if a column is part of PRIMARY KEY:
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>.
If NULL is specified explicitly, we have a choice, because it's non-standard :)
Description: When creating a table, CREATE TABLE automatically and silently changes any columns specified as PRIMARY KEY to NOT NULL, even if they are explicitly stated otherwise. Can this be added to one of the SQL_MODEs, to at least generate a warning? How to repeat: mysql> drop table if exists foo; Query OK, 0 rows affected (0.00 sec) mysql> create table foo (a int null, primary key(a)); Query OK, 0 rows affected (0.00 sec) mysql> show create table foo \G *************************** 1. row *************************** Table: foo Create Table: CREATE TABLE `foo` ( `a` int(11) NOT NULL default '0', PRIMARY KEY (`a`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1 1 row in set (0.00 sec) Suggested fix: Add to some SQL_MODE to generate a warning.