From: Peter Brawley Date: April 27 2005 2:02pm Subject: Re: why NOT NULL in PRIMARY key?? List-Archive: http://lists.mysql.com/mysql/183232 Message-Id: <426F9B92.8060207@earthlink.net> MIME-Version: 1.0 Content-Type: multipart/mixed; boundary="=======AVGMAIL-426F9B931FCB=======" --=======AVGMAIL-426F9B931FCB======= Content-Type: multipart/alternative; boundary=------------010809060703070401080401 --------------010809060703070401080401 Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 7bit Jigal >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. PB ----- Jigal van Hemert wrote: >From: "Mikhail Entaltsev" > >Mikhail, > > > >>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. >> >> > >MySQL as a whole seems a bit more flexible: >a UNIQUE index allows multiple NULL values (which seems to follow the NULL >handling you describe), but with BDB table only a single NULL value is >allowed in a UNIQUE index (so MySQL seems to be able to find a NULL value in >de db). > >Anyway, I gues it's just a question of following a standard and optimizing >the engine according to that standard. > >Regards, Jigal. > > > > --------------010809060703070401080401 Content-Type: text/html; charset=ISO-8859-1 Content-Transfer-Encoding: 7bit Jigal

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

PB

-----

Jigal van Hemert wrote:

From: "Mikhail Entaltsev"

Mikhail,

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.


MySQL as a whole seems a bit more flexible:
a UNIQUE index allows multiple NULL values (which seems to follow the NULL
handling you describe), but with BDB table only a single NULL value is
allowed in a UNIQUE index (so MySQL seems to be able to find a NULL value in
de db).

Anyway, I gues it's just a question of following a standard and optimizing
the engine according to that standard.

Regards, Jigal.

--------------010809060703070401080401-- --=======AVGMAIL-426F9B931FCB======= Content-Type: text/plain; x-avg=cert; charset=us-ascii Content-Transfer-Encoding: quoted-printable Content-Disposition: inline Content-Description: "AVG certification" No virus found in this outgoing message. Checked by AVG Anti-Virus. Version: 7.0.308 / Virus Database: 266.10.2 - Release Date: 4/21/2005 --=======AVGMAIL-426F9B931FCB=======--