Ask the Expert

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

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

There are Comments. Add yours.

 
TIP: Want to include a code block in your comment? Use <pre> or <code> tags around the desired text. Ex: <code>insert code</code>

REGISTER or login:

Forgot Password?
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
Sort by: OldestNewest

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: