how can i create Unique Constraint on one column that allow multiple null values,

Question

Post reply

how can i create Unique Constraint on one column that allow multiple null values,

vineetbhargav

Mr or Mrs. 500

Points: 522
More actions
May 24, 2011 at 3:27 am

#250701

Hi
Please tell me how can i create Unique Constraint on one column that allow multiple null values,I dont want to use the feature of Filter index is there any way to deal with it.
Thanks
Vineet
Thanks And Regards
Vineet Bhargava
vineetbhargav@gmail.com

Viewing 11 posts - 1 through 10 (of 10 total)

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

Latheesh NK SSCertifiable Points: 6559 More actions · Answer 1

you canot enforce unique constraint with multiple null values for a column

spaghettidba SSC Guru Points: 105732 More actions · Answer 2

The only ways to achieve what you're after are:

1. Filtered index

2. Indexed view

I understand that you don't want a filtered index: can you explain why?

Maybe an indexed view can do, but IMHO it is not much different from a filtered index.

Hope this helps

Gianluca

-- Gianluca Sartori

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

Cause filtered index is a part of SQL Sever 2008 and i'm using 2005 🙁

Thanks

Vineet Bhargava

Thanks And Regards
Vineet Bhargava
vineetbhargav@gmail.com

spaghettidba SSC Guru Points: 105732 More actions · Answer 4

vineetbhargav (5/24/2011)
Cause filtered index is a part of SQL Sever 2008 and i'm using 2005 🙁
Thanks
Vineet Bhargava

Then you can use an indexed view.

-- Gianluca Sartori

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

Can you help me out as i have no idea how to use indexed view for this situation

Thanks

Vineet Bhargava

Thanks And Regards
Vineet Bhargava
vineetbhargav@gmail.com

spaghettidba SSC Guru Points: 105732 More actions · Answer 6

Here's an example:

CREATE TABLE dbo.TEST (

id int identity(1,1) PRIMARY KEY CLUSTERED,

uniqueColumn int NULL

)

INSERT INTO dbo.TEST (uniqueColumn)

SELECT 1

UNION ALL

SELECT 2

UNION ALL

SELECT 3

UNION ALL

SELECT 4

UNION ALL

SELECT 5

UNION ALL

SELECT NULL

UNION ALL

SELECT NULL

UNION ALL

SELECT NULL

GO

CREATE VIEW UNQ_TEST

WITH SCHEMABINDING

AS

SELECT uniqueColumn

FROM dbo.TEST

WHERE uniqueColumn IS NOT NULL

GO

CREATE UNIQUE CLUSTERED INDEX IX_uniqueColumn ON UNQ_TEST (uniqueColumn)

GO

-- Gianluca Sartori

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

If i'm not wrong we are creating the Unique Index not Unique Key ...and what will happen if i make the composite column unique Key

Thanks

Vineet Bhargava

Thanks And Regards
Vineet Bhargava
vineetbhargav@gmail.com

spaghettidba SSC Guru Points: 105732 More actions · Answer 8

vineetbhargav (5/24/2011)
If i'm not wrong we are creating the Unique Index not Unique Key ...and what will happen if i make the composite column unique Key
Thanks
Vineet Bhargava

I'm not sure I understand your question. Can you please clarify with an example, using the sample code I posted?

-- Gianluca Sartori

sharath.chalamgari SSCertifiable Points: 5680 More actions · Answer 9

This URL is having an exaple for You

http://weblogs.sqlteam.com/mladenp/archive/2007/05/17/60209.aspx">

http://weblogs.sqlteam.com/mladenp/archive/2007/05/17/60209.aspx

Orlando Colamatteo SSC Guru Points: 182279 More actions · Answer 10

Duplicate post...direct replies to this post since OP confirmed they are on 2005:

http://www.sqlservercentral.com/Forums/Topic1113770-149-1.aspx

There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato