DOCUMENT:Q124483 27-AUG-1999 [odbc] TITLE :INF: Index Types and NULL Values PRODUCT :Open Database Connectivity (ODBC) PROD/VER:WINDOWS:1.0 OPER/SYS: KEYWORDS: ====================================================================== ------------------------------------------------------------------------------- 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 where 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 query words: 2.00.2317 ODBC DESKTOP DATABASE DRIVERS PACK MFC VISUAL C++ Windows NT ====================================================================== Keywords : Technology : kbAudDeveloper kbODBCSearch kbODBC100 Version : WINDOWS:1.0 ============================================================================= 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. Copyright Microsoft Corporation 1999.