我可以为主键设置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:

表上有一个两列主键。我尝试修改它以设置ignore_dup_key:

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.

不能使用索引选项ignore_dup_key来更改索引'PK_mypk'，因为它会强制一个主约束或唯一约束。

How else should I set IGNORE_DUP_KEY to on?

我还应该如何设置IGNORE_DUP_KEY ?

5 个解决方案

#1

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.

它没有在网上的书籍中记录，但是我发现，虽然这对主键有效，但是不能用ALTER INDEX改变它，您必须删除并重新创建主键。

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

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

#2

ALTER TABLE [TableName] REBUILD WITH (IGNORE_DUP_KEY = ON)

#3

It determines what happens when you insert duplicates only

它决定了只插入副本时会发生什么。

See ALTER TABLE..index option

看到ALTER TABLE . .指数的选择

Specifies the error response when an insert operation attempts to insert duplicate key values into a unique index. The IGNORE_DUP_KEY option applies only to insert operations after the index is created or rebuilt. The option has no effect when executing CREATE INDEX, ALTER INDEX, or UPDATE.

指定当插入操作试图将重复的键值插入到唯一索引时的错误响应。IGNORE_DUP_KEY选项只适用于创建或重建索引之后的插入操作。当执行创建索引、修改索引或更新时，该选项无效。

~~..and it does not apply to PKs~~

. .它并不适用于PKs

The BOL comment for ALTER TABLE about this and "backwards compatibility" is somewhat confusing. I just tried it and BradC is correct.

ALTER TABLE的BOL注释和“向后兼容性”有点让人困惑。我刚试过，BradC是对的。

CREATE TABLE dbo.foo (bar int PRIMARY KEY WITH (FILLFACTOR=90, IGNORE_DUP_KEY = ON))
GO
INSERT dbo.foo VALUES (1)
GO
INSERT dbo.foo VALUES (1)
GO
--gives    
(1 row(s) affected)

Duplicate key was ignored.

(0 row(s) affected)

#4

Personally I never want it to ignore the duplicate. If there is a duplicate value to a primary key, it needs to be fixed. I don't want it ignored and the other records inserted because then the user might think that they all got inserted. This setting is a cover-up for a bad insert process. A well designed process doesn't need this setting as it cleans the data before entering it (or uses upsert to update existing and insert new ones) and sends the bad records to a table so that they can be fixed and reinserted or sends an error back to the user, so they they know their record was not inserted.

就我个人而言，我不希望它忽略复制。如果主键有重复的值，则需要对其进行修复。我不希望它被忽略其他记录被插入因为用户可能会认为它们都被插入了。这种设置是对糟糕的插入过程的掩盖。一个精心设计的过程不需要此设置,因为它清理数据在进入它(或使用现有upsert更新和插入新的)并发送坏记录一个表,这样他们可以固定和插入或发送一个错误返回给用户,因此他们他们不知道他们的记录是插入。

#5

Note that this setting only affects what happens if you try to insert a duplicate key, it won't allow you to insert a duplicate key.

注意，这个设置只会影响插入重复键时的情况，它不允许插入重复键。

If you're attempting to insert duplicate keys, you could drop the primary key index, insert your records, fix up the data (remove duplicates, etc.), then recreate the index.

如果要插入重复的键，可以删除主键索引、插入记录、修复数据(删除重复的键等等)，然后重新创建索引。

#1