T-SQL: How do I create a unique key that is case sensitive?

45
votes

How do I create a unique constraint on a varchar field that is case sensitive (SQL Server 2005)?

Currently my constraint looks like this:

alter table MyTable
add constraint UK_MyTable_MyUniqueKey unique nonclustered (MyCol)

When I try to insert the following two values, I get a "Violation of UNIQUE KEY constraint..." error.

insert into MyTable (MyCol) values ('ABC')
insert into MyTable (MyCol) values ('abc') --causes a violation of UNIQUE KEY constraint 'UK_MyTable_MyUnqiueKey'

I would like the two differently-cased values to be handled as unqiue. I imagine it will involve the following code, but I do not know how it changes my add constraint syntax.

COLLATE SQL_Latin1_General_CP1_CS_AS
2
sqlsql-servertsqlconstraints
do you mean case 'sensitive' or insensitive? if ABC and abc both are resolved the same then that is a case 'in'sensitive comparison. You might restate the question – keithwarren7
yes - you are correct. Thank you – Seibar
So, what collation caused "abc" and "ABC" to collide? UTF-8? Do you remember? Thanks. – wha7ever

2 Answers

62
votes

This will change the column to be case sensitive. I don't think there's any change to your constraint...

ALTER TABLE mytable 
ALTER COLUMN mycolumn VARCHAR(10) 
COLLATE SQL_Latin1_General_CP1_CS_AS

Any selects or joins on this column will become case sensitive as a result of this operation.

4
votes

You can only set the case-sensitivity of the data in the database (smallest granularity of column). You can't set case-sensitivity of an index - that would be equivalent to being able to index on an expression, which is possible in some databases but not Sql Server.