Developer allows NULL values in unique constraint definition; does Oracle allow this? Does Oracle consider a NULL...
By submitting your email address, you agree to receive emails regarding relevant topic offers from TechTarget and its partners. You can withdraw your consent at any time. Contact TechTarget at 275 Grove Street, Newton, MA.
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.