SQL Server 唯一索引允许重复
我正在使用 SQL Server 2008，有一个以 id（数字）列作为主键的表。三个 varchar 列上还有一个唯一索引。我能够添加具有完全相同的三列集的行。我通过对值的简单查询进行了验证，并返回了 2 行。
我编辑了索引并添加了 id 列。当我尝试再次编辑它并删除 id 列时，它抱怨存在重复的行，它删除了索引但无法创建它。
然后，我清理重复的数据库，使用相同的 3 个 varchar 重新创建索引作为唯一和非聚集，现在它可以正常工作，不允许重复。
有谁知道为什么忽略该索引的唯一性？
I am using SQL Server 2008, had a table with an id (numeric) column as the primary key. Also had a unique index on three varchar columns. I was able to add a row with the exact same set of the three columns. I verified it with a simple query on the values and 2 rows were returned.
I edited the index and added the id column. When I tried to edit it again and remove the id column it complained that there were duplicate rows, it deleted the index but couldn't create it.
I then clean the database of the duplicated, recreated the index with the same 3 varchars as unique and nonclustered and now it works properly, not allowing duplicates.
Does anyone know why the uniqueness of this index was ignored?
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（2）
该索引可能已被禁用（请参阅禁用索引），您的 '重复的值可能不同（例如尾随空格），或者您的测试可能不正确。
确保您没有在强制唯一索引中插入重复项。
The index could had been disabled (see Disabling Indexes), your 'duplicate' values may had been different (trailing spaces for example), or your test may be incorrect.
For sure you did not insert a duplicate in a enforced unique index.
我不是这个主题的专业人士，但索引的“唯一”设置可能是指索引的构建/存储方式，而不是它是一个约束。这可能也意味着索引的性能不是最优的。
创建索引时，DBMS 可能会检查这一点。
I'm not a pro on this subject, but the 'is unique'-setting of the index probably refers to the way the index is build/stored, not that it is a constraint. This probably also means the performance of the index is sub-optimal.
When creating the index, the DBMS might check this.