sql - Can I set ignore_dup_key on for a primary key?

Question

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?

question from:https://stackoverflow.com/questions/2594193/can-i-set-ignore-dup-key-on-for-a-primary-key

Answer 1

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.

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: 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