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

sql sql-server tsql constraints
Seibar picture Seibar · Jan 27, 2009 · Viewed 32.9k times · Source

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

Answer

Jason Punyon picture Jason Punyon · Jan 27, 2009

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.

Related questions

Can there be constraints with the same name in a DB?

This is a follow-on question from the one I asked here. Can constraints in a DB have the same name? Say I have: CREATE TABLE Employer ( EmployerCode VARCHAR(20) PRIMARY KEY, Address VARCHAR(100) NULL ) CREATE TABLE Employee ( EmployeeID INT PRIMARY KEY, …

Read More
How do I UPDATE from a SELECT in SQL Server?

In SQL Server, it is possible to insert rows into a table with an INSERT.. SELECT statement: INSERT INTO Table (col1, col2, col3) SELECT col1, col2, col3 FROM other_table WHERE sql = 'cool' Is it also possible to update a …

Read More
How do I perform an IF...THEN in an SQL SELECT?

How do I perform an IF...THEN in an SQL SELECT statement? For example: SELECT IF(Obsolete = 'N' OR InStock = 'Y' ? 1 : 0) AS Saleable, * FROM Product

Read More