Q

Working with NULL values in Oracle

Developer allows NULL values in unique constraint definition; does Oracle allow this? Does Oracle consider a NULL value in the constrained column unique? If so, does this mean that multiple NULL values for the constrained are unique?
A simple experiment should be enough to answer your questions. First, I'll create a simple table with two columns and a unique index on those columns.

ORA9I SQL> create table cons_check (
  2  id1 number,
  3  id2 number);

Table created.

ORA9I SQL> create unique index cons_check_uk on cons_check(id1,id2);

Index created.

ORA9I SQL> alter table cons_check add
  2  constraint cons_check_uk unique (id1,id2)
  3  using index;

Table altered.

So let's attempt to answer the first question, can I store NULL values in this table with this unique constraint? Let's see if we can:

ORA9I SQL> insert into cons_check values (null,null);

1 row created.

ORA9I SQL> insert into cons_check values (1,null);

1 row created.

ORA9I SQL> insert into cons_check values (null,2);

1 row created.

From the above, it is clear that a unique constraint does allow NULL values, and combinations of NULL values. For your last question, can we have multiple NULL values?

ORA9I SQL> insert into cons_check values (null,null);

1 row created.

ORA9I SQL> select rownum,id1,id2 from cons_check;

    ROWNUM        ID1        ID2
---------- ---------- ----------
         1
         2          1
         3                     2
         4

As you can see in row number 1 and 4, I have two rows where both columns are null even though there is a unique constraint. The UNIQUE constraint will allow NULL values. In fact, this is the biggest differentiation between a UNIQUE constraint and a PRIMARY KEY constraint (which does not allow NULL values).

This was first published in April 2004

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