Link to home
Start Free TrialLog in
Avatar of ISBTECH
ISBTECHFlag for United States of America

asked on

Make sure a varchar column allows no duplicates in SQL 2005

Goood Morning Experts,

Is there a way that I can set a Varchar column so that it will allow Nulls, but not duplicate values that are not null?
Avatar of Guy Hengel [angelIII / a3]
Guy Hengel [angelIII / a3]
Flag of Luxembourg image

if you have sql 2008+, you can create a filtered (unique) index excluding null values.

otherwise, you need to create a normal index + a trigger that checks and eventually rejects if the insert/update would result in duplicate values
Avatar of ISBTECH

ASKER

I can't make it an index though because that would not allow nulls right?
a normal index does not prohibit nulls.
you cannot do a UNIQUE index without the filtering technique.
>> you cannot do a UNIQUE index without the filtering technique <<

Hmm, that's not really true.  I can think of easy "tricks" that would do what the requestor wants.
Avatar of ISBTECH

ASKER

I don't have SQL 2008, just 2005 for now, what tricks are you suggesting?
ASKER CERTIFIED SOLUTION
Avatar of Scott Pletcher
Scott Pletcher
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
well, it's really a trick to create/use another column and put the index on that one ...
:)
What else to call it?  Calling it a "technique" seems a bit overblown :-)


>> to create/use another use another column and put the index on that one <<

Not true.
The LEADING column in an index is THE key column, so the index is still primarily on the original column, not the new column.  The new column just aids in making the index unique, to satisfy the functional requirements.


Obviously you didn't think of anything like this, since you stated CATEGORICALLY, in TWO different ways:

"otherwise, you need to create a normal index + a trigger that checks and eventually rejects if the insert/update would result in duplicate values"

"you cannot do a UNIQUE index without the filtering technique"


YIKES -- there's HUGE overhead in a trigger as compared to the "trick" above.  

And presumably the requestor didn't want to give up on their business requirement.