我可以为主键设置 ignore_dup_key 吗?
我在一张表上有一个两列主键.我试图改变它以使用以下命令将 ignore_dup_key 设置为打开:
I have a two-column primary key on a table. I have attempted to alter it to set the ignore_dup_key to on with this command:
ALTER INDEX PK_mypk on MyTable
SET (IGNORE_DUP_KEY = ON);
但我收到此错误:
不能使用索引选项 ignore_dup_key 来改变索引 'PK_mypk'，因为它强制执行主要或唯一约束.
我还应该如何将 IGNORE_DUP_KEY 设置为打开?
How else should I set IGNORE_DUP_KEY to on?
推荐答案
它没有记录在联机丛书中，但我发现虽然 IGNORE_DUP_KEY 对主键有效，但您无法更改它带有 ALTER INDEX；您必须删除并重新创建主键.
It's not documented in Books Online, but I've found that while IGNORE_DUP_KEY is valid for Primary Keys, you can't change it with an ALTER INDEX; you'll have to drop and re-create the primary key.
请记住，IGNORE_DUP_KEY 不允许您在唯一索引中实际存储重复行，它只是改变失败方式当您尝试时:
Keep in mind that IGNORE_DUP_KEY doesn't allow you to actually store duplicate rows in a unique index, it simply changes how it fails when you try it:
ON:插入重复键值时会出现警告信息成唯一索引.只有违反唯一性的行约束将失败.
ON: A warning message will occur when duplicate key values are inserted into a unique index. Only the rows violating the uniqueness constraint will fail.
OFF:插入重复键值时会出现错误信息成唯一索引.整个INSERT操作都会滚动返回.
OFF: An error message will occur when duplicate key values are inserted into a unique index. The entire INSERT operation will be rolled back.
来自 http://msdn.microsoft.com/en-us/library/ms175132.aspx
相关文章