Can we use more than one null value for unique key?
Answer Posted / 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 |
Post New Answer View All Answers
What is the use of double ampersand (&&) in sql queries? Give an example
How can you save or place your msg in a table?
What is the difference between left join and right join?
what are the differences among rownum, rank and dense_rank? : Sql dba
What is sqlexception in java?
explain advantages of innodb over myisam. : Sql dba
what is dbms? : Sql dba
Explain what is a subquery ?
Can we use pl sql in mysql?
what are the different type of sql's statements ? : Sql dba
How can check sql version from command line?
How do I install sql?
How much does sqlite cost?
How to look at the current sql*plus system settings?
What can you do with pl sql?