List:General Discussion« Previous MessageNext Message »
From:Mikhail Entaltsev Date:April 26 2005 3:08pm
Subject:Re: why NOT NULL in PRIMARY key??
View as plain text  
Jigal,

create table YourTable
(
 id INT(11),
 name VARCHAR(32),
 value INT(11),
PRIMARY KEY(id,name,value)
)

let's assume that PRIMARY KEY works like you want (accept NULLs)
and we have a row in your table: (id,name,value) = (1,NULL,12)

Then you insert a new row:
insert into YourTable (id,name,value) values (1,NULL,12).

Before inserting MySQL will try to find a record with the same values.
But since comparison with NULL value returns always FALSE
MySQL will think that there is no such record in the table.
After this point you will get 2 identical records in the table.

Mikhail.


----- Original Message ----- 
From: "Jigal van Hemert" <jigal@stripped>
To: "Dawid Kuroczko" <qnex42@stripped>
Cc: <mysql@stripped>
Sent: Tuesday, April 26, 2005 4:47 PM
Subject: Re: why NOT NULL in PRIMARY key??


> From: "Dawid Kuroczko"
>
> > > 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.
> >
> > Because it is a PRIMARY KEY.  I mean phrase 'PRIMARY KEY' means "a key
> > with which each row can be explicitly addressed".  So if you have 2000
> > rows in a table, you can write 2000 SELECT statemens which will use
> > columns in primary key and each of these SELECT statements will
> > return exactly one (different) row.
> With the NULL values included it will still uniquely identify each row...
>
> I would understand it if it would mean that the key as a whole could not
be
> NULL, but the restriction that each column that is part of a PRIMARY KEY
> must have the NOT NULL constraint is not logical.
>
> > If your PRIMARY KEY would allow NULL values, it would not be possible
> > to address these rows with NULL values (*) and therefore it would not
> > be a real primary key, by definiton.  It would be a unique key.
>
> That would be true for the entire key, but not for each part of the key...
>
> Regards, Jigal.
>
>
> -- 
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe:    http://lists.mysql.com/mysql?unsub=1
>

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