表列的唯一约束 [英] Unique constraint on table column

问题描述

我有一个表（其中有数据的现有表），该表有一个UserName列。
我想让这个UserName是唯一的。
所以我添加一个这样的约束：

I'm having a table (an existing table with data in it) and that table has a column UserName. I want this UserName to be unique. So I add a constraint like this:

ALTER TABLE Users 
ADD CONSTRAINT [IX_UniqueUserUserName] UNIQUE NONCLUSTERED ([UserName])

现在我不断收到错误，表中存在重复的用户。
但我已使用以下查询检查数据库：

Now I keep getting the Error that duplicate users exist in this table. But I have checked the database using the following query:

SELECT COUNT(UserId) as NumberOfUsers, UserName 
FROM Users 
GROUP BY UserName, UserId 
ORDER BY UserName

导致所有具有1作为NumberOfUsers的用户列表。所以没有重复。
但是当我检查用户名，他失败我看到以下结果：

This results in a list of users all having 1 as a NumberOfUsers. So no duplicates there. But when I'm checking the username he fails I see the following result:

beluga
béluga

因此，他无法比较e和é或è这就像他忽略了这些，是否有任何方式sql不添加唯一键约束时忽略这些口音。

So apperently he fails to compare an "e" and "é" or "è" ... It's like he ignores these, is there any way that sql doesn't ignore these accents when adding the unique key contraint.

解决方案：

THX给你们我找到了解决方案。
修正了这个问题：

THX to you guys I've found the solution. This fixed the problem:

ALTER TABLE Users 
ALTER COLUMN UserName nvarchar(250) COLLATE SQL_Latin1_General_CP1_CI_AS

推荐答案

您正在使用的排序规则最可能忽略大小写比较。

The collation you are using most likely ignores case and accents when comparing. You'll need to change the collation.

• Latin1_General_CI_AI 忽略大小写和重音符号


• Latin1_General_CI_AS 不会忽略重音符

• Latin1_General_CI_AI Ignores case and accents
• Latin1_General_CI_AS will not ignore accents

SQL Server归类名称列表此处。

List of SQL server collation names here.

这篇关于表列的唯一约束的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！