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 last published in April 2004

Dig Deeper on Oracle database design and architecture

PRO+

Content

Find more PRO+ content and other member only offers, here.

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.

Start the conversation

Send me notifications when other members comment.

By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Please create a username to comment.

-ADS BY GOOGLE

SearchDataManagement

SearchBusinessAnalytics

SearchSAP

SearchSQLServer

TheServerSide

SearchDataCenter

SearchContentManagement

SearchFinancialApplications

Close