List:General Discussion« Previous MessageNext Message »
From:Jigal van Hemert Date:April 26 2005 2:25pm
Subject:Re: why NOT NULL in PRIMARY key??
View as plain text  
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...

> 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.

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