Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase ««12345»»»

Unique constraint on a nullable column Expand / Collapse
Author
Message
Posted Thursday, December 2, 2010 5:38 AM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Today @ 7:41 AM
Points: 3,033, Visits: 926
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.
Post #1029176
Posted Thursday, December 2, 2010 6:28 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 9:58 AM
Points: 5,369, Visits: 9,890
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
Post #1029201
Posted Thursday, December 2, 2010 6:33 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 1:03 AM
Points: 2,526, Visits: 2,401
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).
Post #1029207
Posted Thursday, December 2, 2010 6:39 AM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Saturday, March 15, 2014 1:45 PM
Points: 405, Visits: 286
Very interesting information, and I appreciate it. Way too many answers for this early in the morning though :) Is that 1/256 statistically?
Post #1029209
Posted Thursday, December 2, 2010 6:45 AM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Friday, September 12, 2014 10:44 AM
Points: 21,405, Visits: 9,617
pjdiller (12/2/2010)
Very interesting information, and I appreciate it. Way too many answers for this early in the morning though :) Is that 1/256 statistically?



Yup, but without factoring for the unknown number of correct answers.
Post #1029214
Posted Thursday, December 2, 2010 6:53 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Monday, December 2, 2013 6:30 AM
Points: 346, Visits: 691
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. :)
Post #1029221
Posted Thursday, December 2, 2010 6:56 AM
UDP Broadcaster

UDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP Broadcaster

Group: General Forum Members
Last Login: Sunday, June 29, 2014 11:26 PM
Points: 1,481, Visits: 1,960
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. :)


<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
Post #1029223
Posted Thursday, December 2, 2010 7:03 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Monday, December 2, 2013 6:30 AM
Points: 346, Visits: 691
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. :)


<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. :) I believe Access 97 also had it.

Whatever the case it's about time! (laughing)
Post #1029231
Posted Thursday, December 2, 2010 7:26 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Monday, July 14, 2014 9:47 AM
Points: 2,919, Visits: 2,525
Good question - thought about it until it hurt

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
Post #1029258
Posted Thursday, December 2, 2010 7:37 AM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Monday, September 8, 2014 12:46 PM
Points: 989, Visits: 1,824
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.
Post #1029270
« Prev Topic | Next Topic »

Add to briefcase ««12345»»»

Permissions Expand / Collapse