On 4/26/05, Jigal van Hemert <[EMAIL PROTECTED]> wrote:
> 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.
Because it is a PRIMARY KEY. I mean phrase 'PRIMARY KEY' means "a key
with which each row can be explicitly addressed". So if you have 2000
rows in a table, you can write 2000 SELECT statemens which will use
columns in primary key and each of these SELECT statements will
return exactly one (different) row.
Since PRIMARY KEY is a primary key it cannot have NULL values.
And there can be only one primary key on the table, for the same
reason.
If your PRIMARY KEY would allow NULL values, it would not be possible
to address these rows with NULL values (*) and therefore it would not
be a real primary key, by definiton. It would be a unique key.
(*). Supposedly if there could be only one NULL value per column it
might be possible, but since NULL means "unknown", it should not
be mixed with real values.
>From what you are saying, you need a UNIQUE key, not a PRIMARY KEY.
Regards,
Dawid
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
