How to avoid a “duplicate key” error?

Question

This statement:

INSERT INTO deleteme
SELECT #t.id, 'test' FROM #t
LEFT JOIN  deleteme  ON deleteme.id = #t.id
WHERE deleteme.id IS NULL;

...will fail with a primary key violation in the concurrent scenario (i.e. when inserting the same key that is absent in deleteme from several threads at the same time) with the default transaction isolation level of READ COMMITTED:

Error: Violation of PRIMARY KEY constraint 'PK_DeleteMe'. Cannot insert duplicate key in object 'dbo.deleteme'.

What is the best way to prevent that?

The table looks something like this:

CREATE TABLE [dbo].[DeleteMe](
    [id] [uniqueidentifier] NOT NULL,
    [Value] [varchar](max) NULL,
CONSTRAINT [PK_DeleteMe] PRIMARY KEY ([id] ASC));

Update

From comments:

Why do you have multiple sessions, which can obviously pull the same id from somewhere, using the same permanent table without having some kind of session key to tell them apart? Even without a duplicate error, how are you going to know which rows belong to which session?

This is a stored procedure that is called by an external service that populates data in this table. The service generates the record id and it provides no guarantee that it won't send the same data twice. Or send the same data at the same time.

The database code is supposed to discard all the records with the same id, if one already exists. The service never sends two records with the same id in the same batch.

Answer 1

If you really need to run multiple threads at the same time you can enable the ignore_dup_key option on the primary key.

This will just give a warning instead of an error when an insert would result in a duplicate key violation. But instead of failing it will discard the row(s) that if inserted would cause the uniqueness violation.

CREATE TABLE [dbo].[DeleteMe](
[id] [uniqueidentifier] NOT NULL,
[Value] [varchar](max) NULL,
CONSTRAINT [PK_DeleteMe] 
PRIMARY KEY ([id] ASC) 
WITH (IGNORE_DUP_KEY = ON));

Example on sqlfiddle

Paul White's detailed explanation on the IGNORE_DUP_KEY option. Thanks Paul.

Answer 2

The name of the table DeleteMe suggests that you are accumulating IDs into this table and then periodically delete rows with these IDs from some other permanent table. Is it true?

If it is true, then you can allow DeleteMe to have duplicate IDs. Just have a non-unique index on id instead of unique (and since id is uniqueidentifier it makes sense to make this index non-clustered as well):

CREATE TABLE [dbo].[DeleteMe](
    [id] [uniqueidentifier] NOT NULL,
    [Value] [varchar](max) NULL,
)

CREATE NONCLUSTERED INDEX [IX_ID] ON [dbo].[DeleteMe]
(
    [id] ASC
)

If you use DeleteMe to remove rows from another MainTable like this, then it doesn't matter if id in DeleteMe is unique or not:

DELETE FROM MainTable
WHERE MainTable.ID IN (SELECT id FROM DeleteMe)

BTW, the query to populate DeleteMe becomes much simpler as well:

INSERT INTO DeleteMe (id, Value)
SELECT #t.id, 'test' 
FROM #t