T-SQL：如何创建区分大小写的唯一键？

问题描述

如何在区分大小写的 varchar 字段上创建唯一约束（SQL Server 2005）？

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)

当我尝试插入以下两个值时，我得到一个违反UNIQUE KEY约束...错误。

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'

不同情况下的值被作为unqiue处理。我想象它将涉及以下代码，但我不知道它如何改变我的添加约束语法。

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

推荐答案

这将更改为区分大小写的列。我不认为你的约束有任何改变...

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.

这篇关于T-SQL：如何创建区分大小写的唯一键？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！