CODEWITHSUNDEEP
c#sql.netsql-serversql-server-2012
Bruno
Bruno

Reputation: 4665

Violation of PRIMARY Key (Duplicate key) on a table where the PK is Identity?

So I have this table:

CREATE TABLE [Snapshots].[Crashproof](
    [EmoteCountId] [int] IDENTITY(1,1) NOT NULL,
    [SnapshotId] [int] NOT NULL,
    [Emote] [nvarchar](42) NOT NULL,
    [EmoteCountTypeId] [int] NOT NULL,
    [Count] [decimal](19, 6) NOT NULL,
    CONSTRAINT [PK_SnapshotsCrashproof] PRIMARY KEY CLUSTERED ([EmoteCountId] ASC) ON [PRIMARY],
    CONSTRAINT [FK_SnapshotsCrashproof_Snapshots] FOREIGN KEY ([SnapshotId]) REFERENCES [Snapshots].[Snapshots] ([SnapshotId]) ON DELETE CASCADE,
    CONSTRAINT [FK_SnapshotsCrashproof_EmoteCountTypes] FOREIGN KEY ([EmoteCountTypeId]) REFERENCES [dbo].[EmoteCountTypes] ([EmoteCountTypeId])
) ON [PRIMARY]
GO

and this code that inserts into it:

using (SqlBulkCopy bulkCopy = new SqlBulkCopy(connection, SqlBulkCopyOptions.Default, trans))
{
    bulkCopy.DestinationTableName = "Snapshots.Crashproof";
    bulkCopy.ColumnMappings.Add(new SqlBulkCopyColumnMapping("SnapshotId", "SnapshotId"));
    bulkCopy.ColumnMappings.Add(new SqlBulkCopyColumnMapping("EmoteCountTypeId", "EmoteCountTypeId"));
    bulkCopy.ColumnMappings.Add(new SqlBulkCopyColumnMapping("Emote", "Emote"));
    bulkCopy.ColumnMappings.Add(new SqlBulkCopyColumnMapping("Count", "Count"));

    using (IDataReader reader = ObjectReader.Create(emoteCountTypesToSnapshot))
    {
        bulkCopy.WriteToServer(reader);
    }
}

it runs fine 99.99% of the time (and the bulk copy is done every minute), however I did have an exception once, it was on the last line (bulkCopy.WriteToServer(reader);):

Violation of PRIMARY KEY constraint...Cannot insert duplicate key in object 'Snapshots.Crashproof'. The duplicate key value is (247125).

I get it that bulk inserting directly in the final table is not recommended, I will modify my code to bulk insert into a staging table, then insert from there. But is it what caused this exception?

I really don't understand how a duplicate key can occur on an Identity field :|

Upvotes: 5

Views: 1127

Answers (2)

Facu Mallia
Facu Mallia

Reputation: 36

This could probably be a race condition on the bulk copy.

If I understand correctly, you are running a bulk copy every minute, and yes, most of the time it will work correctly but depending on the batch size you are trying to insert it will take longer than a minute.

So, assuming that. It can happen to collapse one bulk copy to another trying to insert at the same time records with the same key leading to a Violation of PRIMARY KEY constraint.

Upvotes: 0

Codex
Codex

Reputation: 131

My suggestion is to use an ETL product to do these kinds of tasks. They organize everything for you and just do such a cleaner job. Messing around with bulk copys and stored procedures and making staging tables (which i did for years) is just so ugly to me now.

I've been using Pentaho Kettle for the last few years and have totally fallen in love with it. This task would take me mere minutes, or maybe under a minute, to implement, and does all the heavy lifting of sanely moving/transforming data from place to place in big heaps.

I know it's not a direct answer to your question but it's the immediate thing I would suggest if someone came to me at work with this question.

Upvotes: 0

Related Questions