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
Hall of Fame
Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)

Group: General Forum Members
Points: 3910 Visits: 1502
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
SSChampion
SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)

Group: General Forum Members
Points: 14528 Visits: 15980
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 (5.1K reputation)SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)

Group: General Forum Members
Points: 5061 Visits: 3327
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 (443 reputation)SSC-Addicted (443 reputation)SSC-Addicted (443 reputation)SSC-Addicted (443 reputation)SSC-Addicted (443 reputation)SSC-Addicted (443 reputation)SSC-Addicted (443 reputation)SSC-Addicted (443 reputation)

Group: General Forum Members
Points: 443 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
One Orange Chip
One Orange Chip (29K reputation)One Orange Chip (29K reputation)One Orange Chip (29K reputation)One Orange Chip (29K reputation)One Orange Chip (29K reputation)One Orange Chip (29K reputation)One Orange Chip (29K reputation)One Orange Chip (29K reputation)

Group: General Forum Members
Points: 29787 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
Ten Centuries
Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)

Group: General Forum Members
Points: 1277 Visits: 1253
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
SSCommitted
SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)

Group: General Forum Members
Points: 1788 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
Ten Centuries
Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)

Group: General Forum Members
Points: 1277 Visits: 1253
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
Hall of Fame
Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)

Group: General Forum Members
Points: 3772 Visits: 2904
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
UDP Broadcaster
UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)

Group: General Forum Members
Points: 1489 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