At 08:49 AM 4/26/05, Jay Blanchard wrote:
>[snip]
>http://dev.mysql.com/doc/mysql/en/silent-column-changes.html
>mentions that "Columns that are part of a PRIMARY KEY are made NOT NULL
>even
>if not declared that way. "
>
>And http://dev.mysql.com/doc/mysql/en/create-table.html tells me that "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). "
>
>Why is this? I actually do need NULL values (they really are not equal
>to
>'0', etc.), but also need this column as part of the PRIMARY key in an
>InnoDB table...
>It can't have anything to do with the 'uniqueness' of the data, since I
>can
>have a lot of 'zero'-values in the column, as long as the combination of
>columns in the PRIMARY key results in unique values.
>[/snip]
>
>Since NULL is the absence of a value and PRIMARY keys must have a value
>a NULL column cannot be included as a portion of a PRIMARY key. AFAIK
>this is the case with every RDBMS out there. Asking the development team
>might get you a more informative response.
I'm not on dev team, but my understanding of RDMS theory is that retrieving
data via PK will always return a single row from the table. Since NULL
values are never considered equal to each other, allowing them in a column
that is part of PK would bypass this expected behaviour.
