我可以为主键设置ignore_dup_key吗？ [英] Can I set ignore_dup_key on for a primary key?

问题描述

我在表上有一个两列的主键。我试图改变它，以使用以下命令设置 ignore_dup_key ：

  ALTER INDEX PK_mypk on MyTable 
 SET（IGNORE_DUP_KEY = ON）; 
  

但是我收到这个错误：

无法使用索引选项ignore_dup_key来修改索引'PK_mypk'，因为它执行主要或唯一约束。

我应该将 IGNORE_DUP_KEY 设置为on？

解决方案

它没有在书中记录在线，但是我发现，这对于主键有效，您不能用ALTER INDEX进行更改，您必须删除并重新创建主键。

请记住，此标志不允许您实际存储重复的行，它只会更改导致错误：

  ON 
当将重复的键值插入到唯一的
索引中时，将发出警告消息。只有违反唯一性约束的行才会失败。 
 
 OFF 
当将重复的键值插入到
唯一索引中时，将会出现错误信息。整个INSERT操作将被回滚。 
  

From http://msdn.microsoft.com/en-us/library/ms175132.aspx

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);

But I get this error:

Cannot use index option ignore_dup_key to alter index 'PK_mypk' as it enforces a primary or unique constraint.

How else should I set IGNORE_DUP_KEY to on?

解决方案

Its not documented in Books Online, but I've found that while this is valid for Primary Keys, you can't change this with an ALTER INDEX, you'll have to drop and re-create the primary key.

Keep in mind that this flag doesn't allow you to actually store duplicate rows, it simply changes the error that results:

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
An error message will occur when duplicate key values are inserted into a 
unique index. The entire INSERT operation will be rolled back.

From http://msdn.microsoft.com/en-us/library/ms175132.aspx

这篇关于我可以为主键设置ignore_dup_key吗？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！