ISBTECH
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?
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?
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.
>> 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.
Hmm, that's not really true. I can think of easy "tricks" that would do what the requestor wants.
ASKER
I don't have SQL 2008, just 2005 for now, what tricks are you suggesting?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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.
>> 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.
otherwise, you need to create a normal index + a trigger that checks and eventually rejects if the insert/update would result in duplicate values