how many null values will unique key accepts? [Resolved]

Posted by Rajendra.prasad under Sql Server on 4/18/2010 | Views : 35691 | Status : [Member] | Replies : 15
Hi All,

how many null values will unique key accepts in oracle/sql database?

Regards,
Rajendra




Responses

Posted by: Poster on: 4/18/2010 [Member] Starter

Up
0
Down

Resolved
Unlimited null values.

For more details, read the similar post here http://www.dbapool.com/forumthread/topic_6796.html

Thank you

Rajendra.prasad, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Syedshakeer on: 4/18/2010 [Member] Starter

Up
0
Down

Resolved
Unique key column can accept null values as many no.of rows present.

Syed Shakeer Hussain

Rajendra.prasad, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Pandians on: 4/20/2010 [Member] [MVP] Silver

Up
0
Down
Hi Rajendra

Greetings

In SQL Server try the following scripts for better understanding.

"UNIQUE column can have ONLY ONE NULL ", But, NULL and '' (Empty string) are NOT SAME .

USE MASTER


IF OBJECT_ID('TB_SAMPLE','U') IS NOT NULL
DROP TABLE TB_SAMPLE
GO
CREATE TABLE TB_SAMPLE
(
ID INT,
COL1 VARCHAR(20)NULL CONSTRAINT UNI_COL1 UNIQUE)
GO

INSERT TB_SAMPLE VALUES(100,'A')
INSERT TB_SAMPLE VALUES(101,NULL)
INSERT TB_SAMPLE VALUES(102,'')
INSERT TB_SAMPLE VALUES(103,NULL)
GO

Error Message:
Msg 2627, Level 14, State 1, Line 5
Violation of UNIQUE KEY constraint 'UNI_COL1'. Cannot insert duplicate key in object 'dbo.TB_SAMPLE'.

Result :

SELECT *,CASE WHEN COL1 IS NULL THEN 'THE COLUMN ''COL1'' HAS NULL'
WHEN COL1 ='' THEN 'THE COLUMN ''COL1'' HAS EMPTY STRING'
ELSE 'THE COLUMN ''COL1'' HAS SOME VALUE' END 'Explanation'
FROM TB_SAMPLE

ID COL1 Explanation
----------- -------------------- ----------------------------------
100 A THE COLUMN 'COL1' HAS SOME VALUE
101 NULL THE COLUMN 'COL1' HAS NULL
102 THE COLUMN 'COL1' HAS EMPTY STRING

Cheers


Cheers
www.SQLServerbuddy.blogspot.com
iLink Multitech Solutions

Rajendra.prasad, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Pavanandey on: 4/21/2010 [Member] Bronze

Up
0
Down
Only one null value is allowed in a unique key column.

Thanks
Pavan Kumar
Mark Answer if this fits the need

Rajendra.prasad, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Bunty_18 on: 4/21/2010 [Member] Starter

Up
0
Down
Hi,

Only one null value is accepted in Unique Key.

Rajendra.prasad, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Sriramnandha on: 5/21/2012 [Member] Starter | Points: 25

Up
0
Down
UNIQUE KEY:

ONLY ONE NULL VALUES.RESTRICT DUPLICATE VALUES

PRIMARY KEY:

RESTRICT DUPLICATE AND NULL VALUES

REGARDS


sriram

Rajendra.prasad, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Muhsinathk on: 9/13/2012 [Member] Bronze | Points: 25

Up
0
Down
There is no such limit. unique column have any number of null values.

The basic reason is NULL not equal to NULL (like inifinity <> infinity).

Rajendra.prasad, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Muhsinathk on: 9/14/2012 [Member] Bronze | Points: 25

Up
0
Down
Mark as Answer if it helpful to you..That helps other who search the same...

Rajendra.prasad, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Kishork80 on: 5/26/2014 [Member] Starter | Points: 25

Up
0
Down
UNIQUE Key will allow only ONE Entry what ever it may be .
CREATE TABLE TB_SAMPLE
(
ID INT,
COL1 VARCHAR(20) NULL
)
GO

INSERT TB_SAMPLE VALUES(101,NULL)
INSERT TB_SAMPLE VALUES(101,NULL)
INSERT TB_SAMPLE VALUES(101,NULL)

SELECT * FROM TB_SAMPLE

ID COL1
101 NULL
101 NULL
101 NULL
101 NULL

kishor kumar

Rajendra.prasad, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Bandi on: 5/26/2014 [Member] [MVP] Platinum | Points: 25

Up
0
Down
SQL Server will allow ONLY ONE NULL value for a UNIQUE KEY Column, whereas Oracle will allow ANY number of NULL for UNIQUE Key column

Mark This Response as Answer
--
Chandu
http://www.dotnetfunda.com/images/dnfmvp.gif

Rajendra.prasad, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Rakesh433jntu on: 6/6/2014 [Member] Starter | Points: 25

Up
0
Down
It accepts only one NULL value if UNIQUE key constaraint is defined in specific column in a table

Rajendra.prasad, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: nayeemdotnetfunda-27597 on: 7/24/2014 [Member] Starter | Points: 25

Up
0
Down
It will accept only one null value if you try to insert one more it will give the following error
Msg 2627, Level 14, State 1, Line 1
Violation of UNIQUE KEY constraint 'UQ__Tbl_Mst___16EBFA27389DC138'. Cannot insert duplicate key in object 'dbo.Tbl_Mst_Emp1'. The duplicate key value is (<NULL>).

Rajendra.prasad, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Rasikbapotra on: 8/7/2014 [Member] Starter | Points: 25

Up
0
Down
Difference between Primary and Unique is key that Primary key does not allow for null value whereas Unique key allows null only once. Entering extra null value for unique key throws violation exception.

Rajendra.prasad, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Manimaddu on: 8/11/2014 [Member] Starter | Points: 25

Up
0
Down
According to the ANSI standards SQL:92, SQL:1999, and SQL:2003, a UNIQUE constraint should disallow duplicate non-NULL values, but allow multiple NULL values. SQL Server has always implemented a crippled version of this, allowing a single NULL but disallowing multiple NULL values. Please start moving from the current non-standard implementation toward ANSI-standard behaviour for UNIQUE constraints.

Thanks & Regards,
Mani Kumar

Rajendra.prasad, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Saranpselvam on: 8/18/2014 [Member] Starter | Points: 25

Up
0
Down
only one

Rajendra.prasad, if this helps please login to Mark As Answer. | Alert Moderator

Login to post response