List:General Discussion« Previous MessageNext Message »
From:Paul DuBois Date:April 26 2005 2:43pm
Subject:Re: why NOT NULL in PRIMARY key??
View as plain text  
At 16:25 +0200 4/26/05, Jigal van Hemert wrote:
>From: "Paul DuBois"
>
>Hi Paul,
>
>>  A primary key absolutely forbids duplicate values.
>>
>>  Indexes created with the UNIQUE keyword do not allow duplicates, except
>>  for the special case that multiple NULL values are allowed.
>
>I realise that it may (and is) defined in such a way, but it still does not
>explain *why* part of a PRIMARY key might not be NULL. If the combination of
>parts in the PRIMARY key is such that it can uniquely identify a record it
>would be sufficient for a primary key IMHO. It could well be a UNIQUE index
>with the restriction that the complete key (the parts combined) may not be
>NULL...

I'm not sure I understand what you are saying.  But if part of a PRIMARY KEY
could be NULL, then it _wouldn't_ uniquely identify records.


>
>>  Such a unique index therefore cannot be a primary key.   However, for the
>>  purposes you describe above, it sounds like the solution is simply to
>>  define your columns as allowing NULL, and to create your index using
>UNIQUE
>>  rather than PRIMARY KEY.
>
>Unfortunately ther is a big difference in performance between the primary
>and secudary indexes in InnoDB. We made (secundary) indexes and didn't have
>a primary index at all (so MySQL used a 64-bit integer as primary key).
>After we changed the index to primary the performance increased
>considerably.
>Some queries turned out to be quite slow and we found that these relied on
>NULL values. Converting the index to primary silently converted all "NULL"
>constraints to "NOT NULL" for the columns that are part of the primary key
>and converted all NULL values in the db to the appropriate default values
>for the various column types.
>
>Regards, Jigal.


-- 
Paul DuBois, MySQL Documentation Team
Madison, Wisconsin, USA
MySQL AB, www.mysql.com
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