From: MPA

To: Editor

Date: 28 Aug 2003

 

 

Today I found a very strange thing (a feature request on the SourceForge for FirebirdSQL)

 

Does it make sense to have NULL values in "unique" indexes?

 

Here is the response from one of the developers (I Don't blame him, the SQL99 is very fuzzy about this)"

 

NULL is not a value, so I see no contradiction here. SQL-99 allows any number of NULLs in unique constraints

 

 

From: Fabian Pascal

To: MPA

 

You can answer that question for yourself. If NULLs stand for unknown [and that’s the only thing they should stand for, see chapter 10 in PRACTICAL ISSUES IN DATABASE MANAGEMENT], what is it exactly that you are indexing and how should the index treat them--unique values or not? That's exactly what is not known!!!!

 

 

Fabian Pascal and Chris Date Comment: To quote Hugh Darwen

 

Quite simply, as NULLs do not make "sense" at all, it follows that there cannot be any particular place or circumstance in which they make "sense".

 

Fabian Pascal and Hugh Darwen are considering a paper in the DATABASE FOUNDATIONS series titled The Last NULL in the Coffin, intended to be our last and “definitive” statement on the subject, including how to avoid NULLs via correct database design. Aside from that we will no longer address the issue, which we consider closed for now, and will refer to our extensive writings.

 

In the meantime we comment as follows:

 

·   “NULL value” is a contradiction in terms; the very problem with NULLs is that they are not values and, consequently, violate Codd’s most fundamental Information Principle: In a relational database all data should be represented explicitly and in only one way, as values in tables. Consequently, tables with NULLs are not R-tables (see DATABASE FOUNDATIONS Papers #1 and #2 for what a R-table really is) and, therefore, outside the relational model, where all bets are off;

 

·   It is hard to always tell what NULLs stand for in SQL databases and, therefore, what kind of “many-valued logic” underlies them, what operators should be provided, and whether the ones provided are necessary and sufficient (they are neither);

 

·   There are no indexes in standard SQL, and rightly so. Indexes are a physical implementation feature to maximize performance, and not a logical feature pertinent to a data language;

 

·   UNIQUE in SQL does not refer to what we normally understand as ‘unique’ in the real world, therefore, SQL results may not be correct in the real world; in particular, UNIQUE constraints should fail on NULLs in SQL, but do not;

 

 

Posted 11/07/03

 

 

 

[ABOUT] [QUOTES] [LINKS]