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.

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

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

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.

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

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

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

Answer / vamshi

no. because if we use more than one, it leads to
duplicate, which effects the fundamental property of 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

Answer / krishana kumar gupta

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

What is extent clause in table space?

1 Answers   TCS,

Can u create a primary key with out unique index.

8 Answers  

How does pl sql work?

0 Answers  

What is the most important ddl statements in sql are?

0 Answers  

how can i read write files from pl/sql

3 Answers  

What trigger means?

0 Answers  

what is the difference between a web-garden and a web-farm? : Sql dba

0 Answers  

What are the benefits of pl sql?

0 Answers  

Is it possible to sort a column using a column alias?

0 Answers  

How can get second highest salary in sql?

0 Answers  

What is the basic difference between a sql and stored procedure ?

2 Answers   L&T,

What is having clause in sql?

0 Answers