db-derby-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Jeffrey Lichtman <swa...@rcn.com>
Subject Re: Derby, SQL 2003, UNIQUE constraints, unique indices and NULL (long)
Date Wed, 02 Nov 2005 19:46:30 GMT

>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
                                 swazoo@rcn.com
                                 Check out Swazoo Koolak's Web Jukebox at
                                 http://swazoo.com/ 


Mime
View raw message