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,

Viewing 10 posts - 1 through 11 (of 11 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

Orlando Colamatteo SSC Guru Points: 182293 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