Q

Unique indexes and NULLs

I read the answer from Rudy Limeback on unique index and null values (June 05, 2002). He said: "As to your question about whether a UNIQUE column can have more than one NULL, I'm certain the answer is No. NULLs are not just another value, they do have a special interpretation, but if the column is UNIQUE (and assuming it allows NULLs), only one row can have a NULL value."

Theoretically I agree with Mister Limeback. However, I was able to create a table in Oracle 7.3 with a unique index in a column with multiple null values.

Later I read the response of Brian Peasland about the difference between PK and unique index constraints (April 16, 2002). He said: "You could use just a UNIQUE index, but that allows NULL values. And you can have multiple rows with NULL values. Therefore, you can't uniquely identify those rows. That's why just a UNIQUE index does not satisfy a PK constraint". His answer is in accordance with my practical results. You can say that a null is not necessarily equal to another null but could be. Null is UNKNOWN.

How is this ambiguity resolved in a unique index column?


The proof is in the pudding, as the saying goes. So lets see a quick example:

ORA9I SQL> create table test (
  2  id number,
  3  name varchar2(30));

Table created.

ORA9I SQL> create unique index test_id_idx on
test(id);
Index created.

ORA9I SQL> insert into test values (1,'bob');
1 row created.

ORA9I SQL> insert into test values (NULL,'sue');
1 row created.

ORA9I SQL> insert into test values (NULL,'jane');
1 row created.

ORA9I SQL> commit;
Commit complete.
Here, we can clearly see that I've created a table and declared a UNIQUE index on the ID column of that table. You can clearly see that I was able to insert more than one NULL value into that column without raising any errors. Furthermore, let's examine the structure of the index:
ORA9I SQL> analyze index test_id_idx validate
structure;
Index analyzed.

ORA9I SQL> select lf_rows,distinct_keys
  2  from index_stats;

   LF_ROWS DISTINCT_KEYS
---------- -------------
         1             1
We can clearly see that the index only has one key value in it. NULL values are not stored in the index. This is why searching for columns that contain a NULL value will not use any index on that column. They are simply not stored in the index.

"How is this ambiguity resolved in a unique index column?" It is not resolved since there is no ambiguity. A NULL value will not be stored in the index. So there is no ambiguity.

Keep in mind that my above example is only for one column that is indexed. A composite index, made of more than one column is treated differently. It is the combination of the columns that make a difference. Let's revisit our example and see how it works:

ORA9I SQL> create unique index test_idx on
test(id,name);
Index created.

ORA9I SQL> insert into test values (1,'bob');
1 row created.

ORA9I SQL> insert into test values (NULL,'sue');
1 row created.

ORA9I SQL> insert into test values (2,NULL);
1 row created.

ORA9I SQL> commit;
Commit complete.

ORA9I SQL> analyze index test_idx validate structure;
Index analyzed.

ORA9I SQL> select lf_rows,distinct_keys from
index_stats;

   LF_ROWS DISTINCT_KEYS
---------- -------------
         3             3
Here, you can see that I created a table and my UNIQUE index is on two columns. I've inserted three rows into the table, two of which have at least one column NULL. As you can see, the index stores all three rows! Notice that if I insert all NULLS into the table's row, then they are not stored in the index:
ORA9I SQL> insert into test values (NULL,NULL);
1 row created.

ORA9I SQL> insert into test values (NULL,NULL);
1 row created.

ORA9I SQL> commit;
Commit complete.

ORA9I SQL> analyze index test_idx validate structure;
Index analyzed.

ORA9I SQL> select lf_rows,distinct_keys from
index_stats;

   LF_ROWS DISTINCT_KEYS
---------- -------------
         3             3
I hope that clears things up!


This was first published in August 2002

Dig deeper on Oracle database design and architecture

Pro+

Features

Enjoy the benefits of Pro+ membership, learn more and join.

Have a question for an expert?

Please add a title for your question

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.

0 comments

Oldest 

Forgot Password?

No problem! Submit your e-mail address below. We'll send you an email containing your password.

Your password has been sent to:

-ADS BY GOOGLE

SearchDataManagement

SearchBusinessAnalytics

SearchSAP

SearchSQLServer

TheServerSide

SearchDataCenter

SearchContentManagement

SearchFinancialApplications

Close