INF: Index Types and NULL Values

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: