List:General Discussion« Previous MessageNext Message »
From:Frank Bax Date:April 26 2005 1:11pm
Subject:RE: why NOT NULL in PRIMARY key??
View as plain text  
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. 

Thread
why NOT NULL in PRIMARY key??Jigal van Hemert26 Apr
  • Re: why NOT NULL in PRIMARY key??Martijn Tonies26 Apr
  • Re: why NOT NULL in PRIMARY key??Jochem van Dieten26 Apr
  • Re: why NOT NULL in PRIMARY key??Jigal van Hemert26 Apr
  • Re: why NOT NULL in PRIMARY key??Harald Fuchs26 Apr
  • Re: why NOT NULL in PRIMARY key??Jigal van Hemert26 Apr
    • Re: why NOT NULL in PRIMARY key??SGreen26 Apr
      • Att. Shawn GreenJigal van Hemert2 May
  • Re: why NOT NULL in PRIMARY key??Dawid Kuroczko26 Apr
  • Re: why NOT NULL in PRIMARY key??Jigal van Hemert26 Apr
    • Re: why NOT NULL in PRIMARY key??Jochem van Dieten26 Apr
  • Re: why NOT NULL in PRIMARY key??Jigal van Hemert26 Apr
    • Re: why NOT NULL in PRIMARY key??Paul DuBois26 Apr
  • Re: why NOT NULL in PRIMARY key??Martijn Tonies26 Apr
  • Re: why NOT NULL in PRIMARY key??Mikhail Entaltsev26 Apr
  • Re: why NOT NULL in PRIMARY key??Jigal van Hemert26 Apr
  • Re: why NOT NULL in PRIMARY key??Harald Fuchs26 Apr
  • Re: why NOT NULL in PRIMARY key??Martijn Tonies26 Apr
  • Re: why NOT NULL in PRIMARY key??Jigal van Hemert27 Apr
    • Re: why NOT NULL in PRIMARY key??Peter Brawley27 Apr
  • Re: why NOT NULL in PRIMARY key??Jigal van Hemert27 Apr
    • Re: why NOT NULL in PRIMARY key??Frank Bax27 Apr
    • Re: why NOT NULL in PRIMARY key??Peter Brawley27 Apr
    • Re: why NOT NULL in PRIMARY key??Joerg Bruehe27 Apr
  • Re: why NOT NULL in PRIMARY key??Jigal van Hemert28 Apr
RE: why NOT NULL in PRIMARY key??Jay Blanchard26 Apr
  • Re: why NOT NULL in PRIMARY key??Jigal van Hemert26 Apr
    • Re: why NOT NULL in PRIMARY key??Paul DuBois26 Apr
  • RE: why NOT NULL in PRIMARY key??Frank Bax26 Apr
  • Re: why NOT NULL in PRIMARY key??Jigal van Hemert26 Apr
    • Re: why NOT NULL in PRIMARY key??Paul DuBois26 Apr
  • Re: why NOT NULL in PRIMARY key??Martijn Tonies26 Apr
  • Re: why NOT NULL in PRIMARY key??Jigal van Hemert26 Apr
    • Re: why NOT NULL in PRIMARY key??Paul DuBois26 Apr
    • Re: why NOT NULL in PRIMARY key??Peter Brawley26 Apr
RE: why NOT NULL in PRIMARY key??emierzwa26 Apr
  • RE: why NOT NULL in PRIMARY key??SGreen26 Apr
  • Re: why NOT NULL in PRIMARY key??Martijn Tonies26 Apr
RE: why NOT NULL in PRIMARY key??Jay Blanchard26 Apr
  • Re: why NOT NULL in PRIMARY key??Dawid Kuroczko27 Apr
  • Re: why NOT NULL in PRIMARY key??Martijn Tonies27 Apr
RE: why NOT NULL in PRIMARY key??emierzwa26 Apr
Re: why NOT NULL in PRIMARY key??beacker30 Apr
  • Re: why NOT NULL in PRIMARY key??Jigal van Hemert1 May
  • Re: why NOT NULL in PRIMARY key??Martijn Tonies1 May