Can we use more than one null value for unique key?
Answers were Sorted based on User's Feedback
Answer / ashwin
you can insert as many values as u want, in uniquelly
declared column
some people r saying that if u insert more than one null
value in unique column of relation then null is duplicated
which violets unique key property..........well this saying
is totally rubbish......are pehle insert karke dekho phir
bolo as simple as that...
Is This Answer Correct ? | 4 Yes | 0 No |
Answer / shailendra chauhan
Yes, We can insert more the one NULL with unique key.
cos, Neither null is equal to null Nor null is not equal to
null.
Is This Answer Correct ? | 8 Yes | 5 No |
Answer / christina
Yes, We can use more then one null value in unique key ,
because "null" means undefined or unknown so we can add any
number of null values in unique key...
This is the major difference between primary nd unique key,
Primary key doesn't allow null values to be entered into
the columns marked as priamry key and Unique allow us to
enter null values...............
Is This Answer Correct ? | 5 Yes | 2 No |
Answer / sam
Yes ,we can insert more null value in a table having unique
key,Bcoz one null vale is different frm another null value.
Is This Answer Correct ? | 6 Yes | 4 No |
Answer / arika
We can insert duplicate nul values in palce of using unique
key constrint.Because all nulls are not equal.
ex:-
select * from emp where comm=null;
explenation;
here equal operator not support null value because
all nulls are not equal
ex:-
select * from emp where comm is null;
explenation:
'is' operator suport null
Is This Answer Correct ? | 2 Yes | 0 No |
Answer / praveen hr
UNIQUE Constraints
You can use UNIQUE constraints to make sure that no
duplicate values are entered in specific columns that do
not participate in a primary key. Although both a UNIQUE
constraint and a PRIMARY KEY constraint enforce uniqueness,
use a UNIQUE constraint instead of a PRIMARY KEY constraint
when you want to enforce the uniqueness of a column, or
combination of columns, that is not the primary key.
Multiple UNIQUE constraints can be defined on a table,
whereas only one PRIMARY KEY constraint can be defined on a
table.
Also, unlike PRIMARY KEY constraints, UNIQUE constraints
allow for the value NULL. However, as with any value
participating in a UNIQUE constraint, only one null value
is allowed per column.
A UNIQUE constraint can be referenced by a FOREIGN KEY
constraint.
Link from MSDN http://msdn.microsoft.com/en-
us/library/ms191166.aspx
Is This Answer Correct ? | 2 Yes | 1 No |
Answer / a
we have to use computed column to insert multiple null values.
Is This Answer Correct ? | 1 Yes | 0 No |
Answer / raman
Primary Key is a combination of Unique and NOT NULL
Constraints so it can’t have duplicate values or any NUll
Whereas for Oracle UNIQUE Key can have any number of NULL
but for SQL Server It can have only one NULL
Is This Answer Correct ? | 1 Yes | 0 No |
Answer / hassan khan
A unique key constraint does not imply the NOT NULL
constraint in practice. Because NULL is not an actual value
(it represents the lack of a value), when two rows are
compared, and both rows have NULL in a column, the column
values are not considered to be equal. Thus, in order for a
unique key to uniquely identify each row in a table, NULL
values must not be used. According to the SQL standard and
Relational Model theory, a unique key (unique constraint)
should accept NULL in several rows/tuples — however not all
RDBMS implement this feature correctly.
Is This Answer Correct ? | 1 Yes | 0 No |
Answer / sabariesh thavamani
i worked out the coding in the above using oracle 9 i, but
i could not get the correct answer what he told.. try it
SQL> create table sab(name varchar2(10) unique);
Table created.
SQL> insert into sab values('&sab');
Enter value for sab: null
old 1: insert into sab values('&sab')
new 1: insert into sab values('null')
1 row created.
SQL> /
Enter value for sab: null
old 1: insert into sab values('&sab')
new 1: insert into sab values('null')
insert into sab values('null')
*
ERROR at line 1:
ORA-00001: unique constraint (LPUSER18.SYS_C0038068)
violated
SQL> /
Enter value for sab: sabari
old 1: insert into sab values('&sab')
new 1: insert into sab values('sabari')
1 row created.
SQL> /
Enter value for sab: null
old 1: insert into sab values('&sab')
new 1: insert into sab values('null')
insert into sab values('null')
*
ERROR at line 1:
ORA-00001: unique constraint (LPUSER18.SYS_C0038068)
violated
SQL> select * from sab
2 ;
NAME
----------
null
sabari
try it....
Is This Answer Correct ? | 5 Yes | 5 No |
What are the two virtual tables available at the time of database trigger execution?
What is the Diff b/w Constraints and Trigeer
What is the difference between alter trigger and drop trigger statements?
what are different types of keys in sql?
Is postgresql a server?
explain the advantages and disadvantages of stored procedure? : Sql dba
What is the diff between Truncate table / delete <table name> purge
Can a foreign key have a different name?
Suppose I have to create a view on a table, that is not yet been created by DBA. I khow the table structure. Is it possible to create the view before DBA creates this table? If yes then how? Is it possible to create synonym in this way?
What is pivot table in sql?
how will i became good database developer?
What are actual parameters and formal parameters?