For those wondering how unique indices with multiple null values may
be implemented, it can be done pretty simple: The columns with null
values are not part of the index. This will if course affect the
execution plan.
Example:
CREATE TABLE t (i INTEGER, a INTEGER NOT NULL, b INTEGER);
CREATE UNIQUE INDEX idx1 ON t(i);
CREATE UNIQUE INDEX idx2 ON t(a, b);
Here are some more examples, assuming that nulls are not stored in indexes:
CREATE TABLE t2 (i INTEGER, j INTEGER NOT NULL);
CREATE INDEX idx3 on t2(i);
CREATE UNIQUE INDEX idx4 on t2(j);
SELECT i FROM t2; -- use index idx3 (covers query)
SELECT j FROM t2; -- Do a table scan
There is of course are other ways of implementing unique indices with
multiple null values.
--
Bernt Marius Johnsen, Database Technology Group,
Yes. The comparison operations for datatypes are defined in the interface:
org.apache.derby.iapi.types.DataValueDescriptor
This interface defines two comparison methods:
/**
* Compare this Orderable with a given Orderable for the purpose of
* index positioning. This method treats nulls as ordered values -
* that is, it treats SQL null as equal to null and less than all
* other values.
*
* @param other The Orderable to compare this one to.
*
* @return <0 - this Orderable is less than other.
* 0 - this Orderable equals other.
* >0 - this Orderable is greater than other.
*
* The code should not explicitly look for -1, or 1.
*
* @exception StandardException Thrown on error
*/
int compare(DataValueDescriptor other) throws StandardException;
/**
* Compare this Orderable with a given Orderable for the purpose of
* qualification and sorting. The caller gets to determine how nulls
* should be treated - they can either be ordered values or unknown
* values.
*
* @param op Orderable.ORDER_OP_EQUALS means do an = comparison.
* Orderable.ORDER_OP_LESSTHAN
means compare this < other.
*
Orderable.ORDER_OP_LESSOREQUALS means compare this <= other.
* @param other The DataValueDescriptor to compare this one to.
* @param orderedNulls True means to treat nulls as ordered values,
* that is,
treat SQL null as equal to null, and less
* than all other values.
* False means
to treat nulls as unknown values,
* that is, the
result of any comparison with a null
* is the
UNKNOWN truth value.
* @param unknownRV The return value to use if
the result of the
* comparison
is the UNKNOWN truth value. In other
* words, if
orderedNulls is false, and a null is
* involved in
the comparison, return unknownRV.
* This
parameter is not used orderedNulls is true.
*
* @return true if the comparison is true (duh!)
*
* @exception StandardException Thrown on error
*/
boolean compare(
int op,
DataValueDescriptor other,
boolean orderedNulls,
boolean unknownRV)
throws StandardException;
I don't know which compare method the store uses to determine
uniqueness of a data value for a unique index. I suspect it either
uses the first, or it uses the second with orderedNulls equal to
TRUE. In any case, the interface already supports a technique to
allow multiple nulls in a unique index. There may be some reason the
store can't use this technique, though.
- Jeff Lichtman
[EMAIL PROTECTED]
Check out Swazoo Koolak's Web Jukebox at
http://swazoo.com/
