how many null values will unique key accepts? [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

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

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

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

Hi,

Only one null value is accepted in Unique Key.

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

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

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

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

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

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

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

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

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

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

only one

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