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).
Dig Deeper on Oracle database design and architecture
Have a question for an expert?
Please add a title for your question
Get answers from a TechTarget expert on whatever's puzzling you.