SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Unique constraint on a nullable column


Unique constraint on a nullable column

Author
Message
Rune Bivrin
Rune Bivrin
SSCarpal Tunnel
SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)

Group: General Forum Members
Points: 4268 Visits: 1534
Truly a good question because it provoked a lot of thought as to which techniques are possible, valid and practical.

I got it "right" (yay!), but after reading the discussion I think the "adding a where clause in ALL inserts" is very dubious, simply because all inserts may not have a where clause. It could be done if there were no INSERT or UPDATE permissions granted on the table and all access is through stored procedures.


Just because you're right doesn't mean everybody else is wrong.
John Mitchell-245523
John Mitchell-245523
SSC-Dedicated
SSC-Dedicated (35K reputation)SSC-Dedicated (35K reputation)SSC-Dedicated (35K reputation)SSC-Dedicated (35K reputation)SSC-Dedicated (35K reputation)SSC-Dedicated (35K reputation)SSC-Dedicated (35K reputation)SSC-Dedicated (35K reputation)

Group: General Forum Members
Points: 35333 Visits: 16683
If you have a unique constraint on a column, it doesn't matter what you do with triggers, WHERE clauses and so on - you can still only have one NULL. I think your "correct" answers should have included the word "instead", for the avoidance of doubt.

John
Carlo Romagnano
Carlo Romagnano
SSCertifiable
SSCertifiable (7.6K reputation)SSCertifiable (7.6K reputation)SSCertifiable (7.6K reputation)SSCertifiable (7.6K reputation)SSCertifiable (7.6K reputation)SSCertifiable (7.6K reputation)SSCertifiable (7.6K reputation)SSCertifiable (7.6K reputation)

Group: General Forum Members
Points: 7584 Visits: 3400
The only worth of the question is the good news:
"filtered index" (SQL Server 2008).
This feature is present in MS-ACCESS starting from version 1.0 (year 1990).

I run on tuttopodismo
pjdiller
pjdiller
SSC-Addicted
SSC-Addicted (493 reputation)SSC-Addicted (493 reputation)SSC-Addicted (493 reputation)SSC-Addicted (493 reputation)SSC-Addicted (493 reputation)SSC-Addicted (493 reputation)SSC-Addicted (493 reputation)SSC-Addicted (493 reputation)

Group: General Forum Members
Points: 493 Visits: 291
Very interesting information, and I appreciate it. Way too many answers for this early in the morning though Smile Is that 1/256 statistically?
Ninja's_RGR'us
Ninja's_RGR'us
SSC Guru
SSC Guru (68K reputation)SSC Guru (68K reputation)SSC Guru (68K reputation)SSC Guru (68K reputation)SSC Guru (68K reputation)SSC Guru (68K reputation)SSC Guru (68K reputation)SSC Guru (68K reputation)

Group: General Forum Members
Points: 68791 Visits: 9671
pjdiller (12/2/2010)
Very interesting information, and I appreciate it. Way too many answers for this early in the morning though Smile Is that 1/256 statistically?



Yup, but without factoring for the unknown number of correct answers.
roger.plowman
roger.plowman
SSCrazy
SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)

Group: General Forum Members
Points: 2444 Visits: 1383
I had the need for this and used a non-clustered unique filtered index. Simple, efficient, and solves the problem neatly while letting SQL Server enforce it.

As to why you'd ever want to, the reason I did it was because a field was optional (filled with "N/A") but if it was filled in it had to be with a unique value. So by indexing everything *except* N/A I could ensure the unique value of all "actual" entries.

Thank God 2008 finally implemented something Access has had since 2003. Smile
tommyh
tommyh
SSCrazy
SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)

Group: General Forum Members
Points: 2562 Visits: 2000
roger.plowman (12/2/2010)
I had the need for this and used a non-clustered unique filtered index. Simple, efficient, and solves the problem neatly while letting SQL Server enforce it.

As to why you'd ever want to, the reason I did it was because a field was optional (filled with "N/A") but if it was filled in it had to be with a unique value. So by indexing everything *except* N/A I could ensure the unique value of all "actual" entries.

Thank God 2008 finally implemented something Access has had since 2003. Smile


<points a few posts up>
"This feature is present in MS-ACCESS starting from version 1.0 (year 1990)."

Dont know if thats true or not. But i wouldnt be supprised.

/T
roger.plowman
roger.plowman
SSCrazy
SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)

Group: General Forum Members
Points: 2444 Visits: 1383
tommyh (12/2/2010)
roger.plowman (12/2/2010)
I had the need for this and used a non-clustered unique filtered index. Simple, efficient, and solves the problem neatly while letting SQL Server enforce it.

As to why you'd ever want to, the reason I did it was because a field was optional (filled with "N/A") but if it was filled in it had to be with a unique value. So by indexing everything *except* N/A I could ensure the unique value of all "actual" entries.

Thank God 2008 finally implemented something Access has had since 2003. Smile


<points a few posts up>
"This feature is present in MS-ACCESS starting from version 1.0 (year 1990)."

Dont know if thats true or not. But i wouldnt be supprised.

/T


Dunno either. But since the feature was critical to the design of something I know used it in Access 2003 I was being conservative. Smile I believe Access 97 also had it.

Whatever the case it's about time! (laughing)
sjimmo
sjimmo
SSCarpal Tunnel
SSCarpal Tunnel (4.8K reputation)SSCarpal Tunnel (4.8K reputation)SSCarpal Tunnel (4.8K reputation)SSCarpal Tunnel (4.8K reputation)SSCarpal Tunnel (4.8K reputation)SSCarpal Tunnel (4.8K reputation)SSCarpal Tunnel (4.8K reputation)SSCarpal Tunnel (4.8K reputation)

Group: General Forum Members
Points: 4842 Visits: 2907
Good question - thought about it until it hurtHehe

Why would you want to use a where clause to enforce uniqueness? That one stumps me. Otherwise, I agree with the rest of the answers.

Steve Jimmo
Sr DBA
“If we ever forget that we are One Nation Under God, then we will be a Nation gone under." - Ronald Reagan
jeff.mason
jeff.mason
SSCrazy
SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)

Group: General Forum Members
Points: 2737 Visits: 2137
sjimmo (12/2/2010)
Why would you want to use a where clause to enforce uniqueness? That one stumps me.


It stumps you because you wouldn't want to do that. Pure and simple and that's why no one is getting this right hardly. You'd have to have something to guarantee that all of the inserts followed this pattern, and the closest thing to that is a trigger, thus it is one of the options. But once you give out INSERT permission, you lose control over INSERT statements.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search