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?

    Requires Free Membership to View

    Login

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

Join the conversationComment

Share
Comments

    Results

    Contribute to the conversation

    All fields are required. Comments will appear at the bottom of the article.
    Back to top