How can i create a Unique constraint that allow multiple nulls values

Question

How can i create a Unique constraint that allow multiple nulls values

vineetbhargav

Mr or Mrs. 500

Points: 522
More actions
May 23, 2011 at 11:43 pm

#250698

Hi
Please tell me how can i create Unique Constraint on one column that allow multiple null values,
Thanks
Vineet
Thanks And Regards
Vineet Bhargava
vineetbhargav@gmail.com

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

sunitkrishna SSCrazy Points: 2233 More actions · Answer 1

Unique keys do not allow NULL values.As 'NULL' values cant be compared to check for uniqueness,the values of the unique column are considered to be equal.This means that a column defined as a unique key column can have only one NULL value.

---
Thinking is the hardest work there is, which is the probable reason so few engage in it.

Sunitha
😎

vineetbhargav Mr or Mrs. 500 Points: 522 More actions · Answer 2

Thanks Sunitha

i know , But there must be some way to deal with it.

Regards

Vineet Bhargava

Thanks And Regards
Vineet Bhargava
vineetbhargav@gmail.com

steveb. SSC-Forever Points: 46733 More actions · Answer 3

you could wirte a trigger to do this, but i am not sure why you would want to,

also it is considered bad form to post the same question twice.

ChrisM@home SSC-Insane Points: 24260 More actions · Answer 4

Use a filtered unique index.

Edit - quote captured wrong post.

[font="Arial"]^{Low-hanging fruit picker and defender of the moggies}[/font]

For better assistance in answering your questions, please read this[/url].

Understanding and using APPLY, (I)[/url] and (II)[/url] Paul White[/url]

Hidden RBAR: Triangular Joins[/url] / The "Numbers" or "Tally" Table: What it is and how it replaces a loop[/url] Jeff Moden[/url]

Mark Cowne One Orange Chip Points: 26952 More actions · Answer 5

Few possibilities

1) Use a unique constraint on a schemabound view

CREATE VIEW dbo.myView

WITH SCHEMABINDING AS

SELECT col

FROM myTable

WHERE col IS NOT NULL

GO

CREATE UNIQUE CLUSTERED INDEX IX ON dbo.myView (col);

2) Have a look at the 'nullbuster' method

3) Use a filtered index (SQL Server 2008 only)

____________________________________________________

Deja View - The strange feeling that somewhere, sometime you've optimised this query before

How to get the best help on a forum

http://www.sqlservercentral.com/articles/Best+Practices/61537

sunitkrishna SSCrazy Points: 2233 More actions · Answer 6

But a filtered index is possible only in SQL 2008.