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
-- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
