Can we use more than one null value for unique key?

Question Posted / vamshi

31 Answers
64143 Views
A1 Technology, Wipro, I also Faced
E-Mail Answers

Answers were Sorted based on User's Feedback

Can we use more than one null value for unique key?..

Answer / mukesh

Ques:= Can we use more than one null value for unique key?
Ans:=Yes, we can use multiple Null values in case of Unique Key
Column.

SQL> create table abcd(name varchar2(10) unique);

Table created.

SQL> insert into abcd(name)values('amit');

1 row created.

SQL> insert into abcd(name)values('MKS');

1 row created.

SQL> insert into abcd(name)values(null);

1 row created.

SQL> insert into abcd(name)values(null);

1 row created.

SQL> insert into abcd(name)values(null);

1 row created.

SQL> insert into abcd(name)values('');

1 row created.

SQL> insert into abcd(name)values('');

1 row created.

SQL> insert into abcd(name)values('');

1 row created.

SQL> select * from abcd order by name desc;

NAME
----------

amit
MKS

8 rows selected.

Note:= Here blank space above represents the entries of null
statements..!!

SQL> select nvl(name,0) from abcd;

NVL(NAME,0
----------
amit
MKS
0
0
0
0
0
0

8 rows selected.

SQL> select nvl(name,'a') from abcd;

NVL(NAME,'
----------
amit
MKS
a
a
a
a
a
a

8 rows selected.

Is This Answer Correct ?

2 Yes

2 No

Can we use more than one null value for unique key?..

Answer / millerdq

The argument that no two nulls are equal does not prove
anything because it's also true that no two nulls are
different.

if null <> null ..... is false

So, one could also claim that a unique key requires that
all values are unique, given that two nulls are not unique,
it is therefore not allowed.

The fact is, ansi standard says multiple nulls are
permitted in a unique key. However, not all dbms's are
compliant. Oracle permits it; SQL Server does not.

So, best answer...it depends on the dbms

Is This Answer Correct ?

2 Yes

2 No

Can we use more than one null value for unique key?..

Answer / dqm

In theory, a unique key may not contain any nulls. The
reason is simple: the key, by definition is an identifier
and it is illogical to identify something by an unknown.

In practice, some SQL databases permit nulls in unique
keys. Oracle, in particular, allows nulls as long as no
duplicate indices are created in the underlying unique
index. Since Oracle does not create a indices if all
columns are null, the floodgates are open.

For example, for a single column key, this is acceptable
because only the first row makes it to the index:

1
null
null

For a two column key, this is acceptable because in the
first two rows, the first column is unique and the last two
rows, being entirely null, are not represented in the index.

1, null
2, null
null, null
null, null

However, with the same two-column index, this is
unacceptable because the first two rows produce duplicate
inices:

1, null
1, null
null, null
null, null

Is This Answer Correct ?

1 Yes

1 No

Can we use more than one null value for unique key?..

Answer / anjani kumar

Yes,Multiple Null values are allowed in Unique key
Constraint because each Null is different from another Null
so there is no case of duplication.

Is This Answer Correct ?

2 Yes

3 No

Can we use more than one null value for unique key?..

Answer / ramesh

Yes,We should .That's the significance of Unique Key

If you insert data into Unique Key column it checks for the
uniqueness of THIS NON NULL UNIQUE COLUMNAR DATA

Is This Answer Correct ?

13 Yes

15 No

Can we use more than one null value for unique key?..

Answer / guest

No, we cannot use more than one null value in unique key.
This is the major difference between primary key and unique
key.

Primary key cannot contain null values

Whereas unique key can contain only one null value

Is This Answer Correct ?

5 Yes

7 No

Can we use more than one null value for unique key?..

Answer / iftekharul haque

no because primary key is the unique value of the table that
can not allow null&duplicate value.
so it is not possiblity use more than one null value and
duplicate valu

Is This Answer Correct ?

5 Yes

12 No

Can we use more than one null value for unique key?..

Answer / vamshi

no. because if we use more than one, it leads to
duplicate, which effects the fundamental property of unique
key.

Is This Answer Correct ?

61 Yes

71 No

Can we use more than one null value for unique key?..

Answer / ashwani kumar(kannouj)

Unique in similar to primary key but one difference's
primary key duplicate records not allow and not null value
and uniquie allow one null value and duplicate records

Is This Answer Correct ?

6 Yes

25 No

Can we use more than one null value for unique key?..

Answer / krishana kumar gupta

no, because duplicate value is not allowed in unique. so
thats why we can enter only one null value.

Is This Answer Correct ?

10 Yes

31 No

Post New Answer