List:General Discussion« Previous MessageNext Message »
From:Peter Brawley Date:April 27 2005 3:38pm
Subject:Re: why NOT NULL in PRIMARY key??
View as plain text  
Jigal,

I think MS-SQL and Oracle provide switches for treating NULLs as values, 
which is what your proposal amounts to. It seems to me that much of the 
performance advantage you are counting on from PKs would go away if PKs 
could have NULLs and if NULL were a value. IAC I hope MySQL doesn't go 
that way.

PB

-----


Jigal van Hemert wrote:

>From: "Peter Brawley"
>
>Peter,
>
>  
>
>> >Anyway, I gues it's just a question of following a standard and
>>    
>>
>optimizing
>  
>
>> >the engine according to that standard.
>>
>>Your proposal would permit dupe primary keys. It's a question of
>>preventing them.
>>    
>>
>
>Sorry, but I disagree.
>If NULL handling is not done by the table engine but by the rest of MySQL
>then MySQL can compare two NULLs and can act in different ways depending on
>the situation (UNIQUE index in BDB can only have a single NULL entry, but
>MySQL allows multiple NULLs in other table types)
>
>So, if we would define that the key entry "0-NULL-Whatever" equals
>"0-NULL-Whatever" (which MySQL is capable of if you look at the definition
>of UNIQUE indexes with BDB tables) then allowing NULLs as part of a key
>entry would not permit duplicate entries. At least not more than allowing
>other values.
>
>Because there is a considerable performance difference between primary and
>secudary keys in InnoDB it would enable more flexible primary keys that can
>also be used for searching.
>
>It would mean breaking with the standard (PRIMARY = UNIQUE + NOT NULL), so
>it's not likely that it will ever be introduced ;-)
>
>Anyway, I need NULLs in the db (for various reasons which we haven't been
>able to solve in other ways) but I need fast searching on name/value. So I
>have to figure out a different method.
>
>Thanks for thinking about this!
>
>Regards, Jigal.
>
>
>
>
>  
>

Attachment: [text/html]
No virus found in this outgoing message. Checked by AVG Anti-Virus. Version: 7.0.308 / Virus Database: 266.10.3 - Release Date: 4/25/2005
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