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?

Solution 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

Solution 2

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

Solution 3

Drop the PK and then recreate it

ALTER TABLE TableName DROP CONSTRAINT PK_TableName
GO
ALTER TABLE TableName ADD CONSTRAINT PK_TableName PRIMARY KEY CLUSTERED
( MyIDColumn ASC )
WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
GO

SEE -> https://eitanblumin.com/2018/10/28/the-ignore_dup_key-option-in-primary-keys-and-unique-indexes/

Solution 4

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)

Solution 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.

View more solutions

Comments

[Mr. Flibble]

My issue is this: I need to insert a large number of records daily and I'm currently doing an insert command for each record. If the record already exists then it is ignored (by throwing a duplicate key error). From a performance POV it is better to attempt an insert and allow some to fail than it is to check if the record exists before inserting. The next step to improve performance is to use SqlBulkCopy to do the inserts instead of a single command for each record. But this isn't possible if any record exists as the whole batch fails if ignore_dup_key is off.

[julealgon]

What if one of the columns on my primary key is user defined? And what if I need to support insertions of multiple entities at the same time, say 1000? How would I validate that all entries are unique before insertion without a heavy hit to the server? I think the ignore duplicate option is quite appropriate in that situation. Do you disagree?

[dburges]

Yes, you don'thave primary key if you ignore duplicates and if you don;thavea a primary key then you are going to have a world of hurt with bad data eventually. It is a better practice to put the data into a staging table and then use a select statement to insert only the new records. We insert million of records this way.

[Bhindi]

It's not working with Postgresql - 9.5 PSQL doesn't understand the keyword IGNORE_DUP_KEY. Is there any way to make it (or something similar) work in Postgresql >= 9.5

[Kvasi]

Don't know. This question is flagged sql-server, so try searching for similar questions for PostgreSQL.

[pedram bashiri]

Your command turns the 'IGNORE_SUP_KEY' OFF. You need to change it to WITH (IGNORE_DUP_KEY = ON) ON [PRIMARY]

[CWagner]

Just in case someone else encounters this: At least in my case, IGNORE_DUP_KEY is far less performant than doing WHERE NOT EXISTS, so make sure to check for yourself.