表列的唯一约束
我有一个表（其中包含数据的现有表），并且该表有一列 UserName。 我希望这个用户名是唯一的。 所以我添加了这样的约束：
ALTER TABLE Users
ADD CONSTRAINT [IX_UniqueUserUserName] UNIQUE NONCLUSTERED ([UserName])
现在我不断收到此表中存在重复用户的错误。 但我已经使用以下查询检查了数据库：
SELECT COUNT(UserId) as NumberOfUsers, UserName
FROM Users
GROUP BY UserName, UserId
ORDER BY UserName
这会生成一个用户列表，其中 NumberOfUsers 全部为 1。 所以那里没有重复的。 但是当我检查用户名时，他失败了，我看到以下结果：
beluga
béluga
所以显然他无法比较“e”和“é”或“è”...就像他忽略了这些，有什么办法让sql不这样做添加唯一键约束时不要忽略这些重音。
解决方案：
谢谢你们，我已经找到了解决方案。 这解决了问题：
ALTER TABLE Users
ALTER COLUMN UserName nvarchar(250) COLLATE SQL_Latin1_General_CP1_CI_AS
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
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
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.
SOLUTION:
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
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（5）
您使用的排序规则在比较时很可能会忽略大小写和重音符号。 您需要更改排序规则。
Latin1_General_CI_AI忽略大小写和重音Latin1_General_CI_AS不会忽略重音SQL Server 排序规则名称列表 此处。
The collation you are using most likely ignores case and accents when comparing. You'll need to change the collation.
Latin1_General_CI_AIIgnores case and accentsLatin1_General_CI_ASwill not ignore accentsList of SQL server collation names here.
您的查询也按 UserID 分组 - 您不希望这样做。
使用：
您的查询只会显示具有相同名称和相同用户 ID 的用户。 或者，也许按
COUNT(*)对数据进行排序，这样显示的最后一行很可能是麻烦制造者？正如其他人所建议的那样，您也可能会遇到排序问题，但通常情况下，GROUP BY 会是自洽的。
Your query groups by UserID too - you don't want to be doing that.
Use:
Your query would only show up users with the same name and same user ID. Or, maybe, order the data by
COUNT(*)so the last row that shows up is most likely the troublemaker?You could also have problems with collation as others have suggested, but normally, GROUP BY would be self-consistent.
据推测 UserId 是您的主键。 由于它是您分组依据的一部分，因此保证您每组获得一行。 将“userId”列从您的分组依据中取出。
Presumably UserId is your primary key. Since it's part of what you are grouping by, you are guaranteed to get a single row per group. Take the "userId" column out of your group by.
正如 Andrew Barrett 所说，MySQL 中的默认排序规则无法正确识别重音。
将字段的排序规则更改为
UTF8_unicode_ci，它应该可以正确看到重音符号。ci 表示不区分大小写，如果大小写很重要，您可以使用不同的排序规则。
您可以使用新排序规则创建一个新表，然后将 * 从现有表复制到新表中。
As Andrew Barrett says, the default collation in MySQL doesn not recognize accents correctly.
Change the collation of your fields to
UTF8_unicode_ciand it should see accents properly.ci means case insensitive, and you can use a different collation if case is important.
You can create a new table with the new collation, then copy * from the existing table into the new one.
另请注意，您还可以仅创建您对相关排序规则感兴趣的表（而不是服务器范围）。因此您也可以执行以下操作：
Also note that you can also create just the table you are interested in the relevent collation (instead of server wide) .So you could also do something like :