INF: Index Types and NULL Values

Last reviewed: September 18, 1996
Article ID: Q124483
The information in this article applies to:
  • Microsoft Open Database Connectivity, version 1.0

SUMMARY

This article discusses the use of the indexing options DISALLOW NULL and UNIQUE and how NULL values are treated with these indexes.

MORE INFORMATION

The CREATE INDEX syntax for ODBC Desktop Database Drivers is as follows:

   CREATE [UNIQUE] INDEX index-name
   ON table-name (column-identifier [ASC][DESC]
                  [, column-identifier [ASC][DESC]...])
   WITH <index option list>

where <index option list> can be: PRIMARY|DISALLOW NULL|IGNORE NULL

Only the Microsoft Access driver uses the DISALLOW NULL and IGNORE NULL index options. The Btrieve, dBASE, Microsoft FoxPro, and Paradox drivers accept the syntax, but ignore the presence of either option.

If the DISALLOW NULL option is used, an index is created such that NULL values are no longer allowed in the column that comprise the index. Because the Microsoft Access driver does not support the NOT NULL constraint in a CREATE TABLE statement, the use of DISALLOW NULL becomes a good way to achieve the same effect.

However, if a set of columns do not allow NULLs because they have a DISALLOW NULL type of index, SQLColumns still returns NULLABLE=1 on these columns. Thus, even though NULLs are not permitted in these columns because of the type of index assigned to them, SQLColumns does not recognize this. This is because SQLColumns reports the nullability (or lack thereof) of the underlying column and does not take into account the semantics of an index of that column.

Creating a UNIQUE index will result in the column not allowing duplicate values. However, note the following behavior with respect to NULL values:

   If a table test has a UNIQUE index on col1, then it is possible to
   insert two rows into test, such that both the rows have NULL in col1.
   This is a result of the semantics of a NULL value. By definition, a
   NULL value represents something that is unknown. Since two unknown
   values are not necessarily the same, two NULL values are not equal to
   each other. Thus, the uniqueness criterion on the column is not
   violated.


Additional reference words: 2.00.2317 ODBC DESKTOP DATABASE DRIVERS
PACK MFC VISUAL C++ Windows NT
KBCategory: kbprg
KBSubcategory:



THE INFORMATION PROVIDED IN THE MICROSOFT KNOWLEDGE BASE IS PROVIDED "AS IS" WITHOUT WARRANTY OF ANY KIND. MICROSOFT DISCLAIMS ALL WARRANTIES, EITHER EXPRESS OR IMPLIED, INCLUDING THE WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE. IN NO EVENT SHALL MICROSOFT CORPORATION OR ITS SUPPLIERS BE LIABLE FOR ANY DAMAGES WHATSOEVER INCLUDING DIRECT, INDIRECT, INCIDENTAL, CONSEQUENTIAL, LOSS OF BUSINESS PROFITS OR SPECIAL DAMAGES, EVEN IF MICROSOFT CORPORATION OR ITS SUPPLIERS HAVE BEEN ADVISED OF THE POSSIBILITY OF SUCH DAMAGES. SOME STATES DO NOT ALLOW THE EXCLUSION OR LIMITATION OF LIABILITY FOR CONSEQUENTIAL OR INCIDENTAL DAMAGES SO THE FOREGOING LIMITATION MAY NOT APPLY.

Last reviewed: September 18, 1996
© 1998 Microsoft Corporation. All rights reserved. Terms of Use.