Will Oracle Unique Key Constraint Allow / Accept more than One NULL Value?

advertisements

_____________________________________________________________________________________________________________________

What is Unique Key Constraint?

Unique key is one of the 5 integrity constraints. Oracle Unique key can be the combination of one or more than one (upto 32) columns. This key ensures the data uniqueness for the columns specified in the key. Which means the same combination cannot be repeated for a specified unique key.

Unique key can be created using create table or alter table commands.

The differences between primary key and unique key are

a.   unique key will allow NULL in the columns.

b.   Multiple unique key can be created for a table, but only one primary key allowed for a table

Now come to the point, Unique key will accept more than one NULL values to the unique key columns. Each null is considered as unique or different in nature. See one example below.

SQL> create table uniq_test (empno number(4), ename varchar2(10), Address varchar2(30),

constraint UK_uniq_test  unique(address));

  2

Table created.

SQL>  insert into uniq_test values (123,'Jain',NULL);

1 row created.

SQL>  insert into uniq_test values (123,'John',NULL);

1 row created.

SQL> insert into uniq_test values (123,'James','ABC');

1 row created.

SQL> insert into uniq_test values (123,'Jins','ABC');

insert into uniq_test values (123,'Jins','ABC')

*

ERROR at line 1:

ORA-00001: unique constraint (SCOTT.UK_UNIQ_TEST) violated

_____________________________________________________________________________________________________________________

Website Stats