List:General Discussion« Previous MessageNext Message »
From:Joerg Bruehe Date:April 27 2005 7:23pm
Subject:Re: why NOT NULL in PRIMARY key??
View as plain text  
Hi,


let me fall in here.


Jigal van Hemert wrote:
> From: "Peter Brawley"
> 
> Peter,
> 
> 
>> >[[...]]
>>
>>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)

The handling of values (equality, ordering, ...) should be done at the 
lowest possible level, for performance reasons.

If a system acts "in different ways depending on the situation", it 
lacks systematic properties and is difficult to use and to explain.
(Correct, often the situation is important, but if you define the 
equality of values as depending on the "key" property of the column then 
you open a can of worms!)

BDB is a special case which you should not take as a model IMO.

> 
> So, if we would define that the key entry "0-NULL-Whatever" equals
> "0-NULL-Whatever" [[...]]

Your best way of reaching this is to use some other valuefor the purpose 
you were going to use NULL for.

Let me add an aspect which I did not find mentioned yet:
The SQL syntax differs for NULL and non-NULL values!
As long as your key columns have the "NOT NULL" property, you can alwas 
say "WHERE keycol = value".
When you qualify by a NULL value, you need to say "col IS NULL". This 
will add complexity to your application(s).

> 
> [[...]]
> 
> It would mean breaking with the standard (PRIMARY = UNIQUE + NOT NULL), so
> it's not likely that it will ever be introduced ;-)

I sure hope it will not be, for various reasons.

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

I hope you get it solved!


Regards,
Jörg


-- 
Joerg Bruehe, Senior Production Engineer
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