Can I set ignore_dup_key on for a primary key?
ALTER TABLE [TableName] REBUILD WITH (IGNORE_DUP_KEY = ON)
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
It determines what happens when you insert duplicates only
See ALTER TABLE..index option
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.
..and it does not apply to PKs
The BOL comment for ALTER TABLE about this and "backwards compatibility" is somewhat confusing. I just tried it and BradC is correct.
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)