Error when inserting data in non-unique index

Question

I have a batch job that synchronizes data between two identical databases using the MERGE statement. Sometimes, I get the following error: cannot insert duplicate key row in object 'dbo.MatchPlayerStatistics' with unique index 'IX_MatchPlayerStatistics_Player'. That does not make sense because IX_MatchPlayerStatistics_Player is not a unique index, and I am able to insert the exact same data manually. If I drop the index, insert the data, then recreate the index, it works fine.

Why does this error occur and how can I prevent it?

More information

The creation script for the table is the following:

CREATE TABLE [dbo].[MatchPlayerStatistics](
    [ExternalID] [nvarchar](50) NULL,
    [ProviderID] [int] NOT NULL,
    [MatchID] [int] NOT NULL,
    [PlayerPersonID] [int] NOT NULL,
    [TeamID] [int] NOT NULL,
    [YellowCards] [smallint] NULL,
    [DoubleYellowCards] [smallint] NULL,
    [RedCards] [smallint] NULL,
    [Fouls] [smallint] NULL,
    [Goals] [smallint] NULL,
    [PenaltyGoals] [smallint] NULL,
    [PenaltiesMissed] [smallint] NULL,
    [PenaltiesSaved] [smallint] NULL,
    [Shots] [smallint] NULL,
    [Attacks] [smallint] NULL,
    [Corners] [smallint] NULL,
    [Offsides] [smallint] NULL,
    [Assists] [smallint] NULL,
    [OwnGoals] [smallint] NULL,
    [GoalsConcedeed] [smallint] NULL,
    [CreatedBy] [int] NOT NULL,
    [CreatedOn] [datetime] NOT NULL,
    [ModifiedBy] [int] NOT NULL,
    [ModifiedOn] [datetime] NOT NULL,
 CONSTRAINT [PK_MatchPlayerStatistics] PRIMARY KEY CLUSTERED 
(
    [ProviderID] ASC,
    [MatchID] ASC,
    [PlayerPersonID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]


CREATE NONCLUSTERED INDEX [IX_MatchPlayerStatistics_Player] ON [dbo].[MatchPlayerStatistics] 
(
    [ProviderID] ASC,
    [PlayerPersonID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]

The MERGE statement is the following:

MERGE MatchPlayerStatistics AS M
USING   (
    SELECT [ExternalID]
          ,[ProviderID]
          ,[MatchID]
          ,[PlayerPersonID]
          ,[TeamID]
          ,[YellowCards]
          ,[DoubleYellowCards]
          ,[RedCards]
          ,[Fouls]
          ,[Goals]
          ,[PenaltyGoals]
          ,[PenaltiesMissed]
          ,[PenaltiesSaved]
          ,[Shots]
          ,[Attacks]
          ,[Corners]
          ,[Offsides]
          ,[Assists]
          ,[OwnGoals]
          ,[GoalsConcedeed]
          ,[CreatedBy]
          ,[CreatedOn]
          ,[ModifiedBy]
          ,[ModifiedOn]
     FROM [Replication].MatchPlayerStatistics 
     WHERE ProviderID = 1 

        ) AS R
ON (M.MatchID = R.MatchID AND M.PlayerPersonID = R.PlayerPersonID  AND  M.ProviderID = R.ProviderID ) 
WHEN NOT MATCHED 
    THEN 
        INSERT  ([ExternalID]
                ,[ProviderID]
                ,[MatchID]
                ,[PlayerPersonID]
                ,[TeamID]
                ,[YellowCards]
                ,[DoubleYellowCards]
                ,[RedCards]
                ,[Fouls]
                ,[Goals]
                ,[PenaltyGoals]
                ,[PenaltiesMissed]
                ,[PenaltiesSaved]
                ,[Shots]
                ,[Attacks]
                ,[Corners]
                ,[Offsides]
                ,[Assists]
                ,[OwnGoals]
                ,[GoalsConcedeed]
                ,[CreatedBy]
                ,[CreatedOn]
                ,[ModifiedBy]
                ,[ModifiedOn])
         VALUES
               ( R.[ExternalID]
                ,R.[ProviderID]
                ,R.[MatchID]
                ,R.[PlayerPersonID]
                ,R.[TeamID]
                ,R.[YellowCards]
                ,R.[DoubleYellowCards]
                ,R.[RedCards]
                ,R.[Fouls]
                ,R.[Goals]
                ,R.[PenaltyGoals]
                ,R.[PenaltiesMissed]
                ,R.[PenaltiesSaved]
                ,R.[Shots]
                ,R.[Attacks]
                ,R.[Corners]
                ,R.[Offsides]
                ,R.[Assists]
                ,R.[OwnGoals]
                ,R.[GoalsConcedeed]
                ,R.[CreatedBy]
                ,R.[CreatedOn]
                ,R.[ModifiedBy]
                ,R.[ModifiedOn])


WHEN MATCHED 
    THEN 
        UPDATE 
               SET   [ExternalID] = R.[ExternalID]
                    ,[ProviderID] = R.[ProviderID]
                    ,[MatchID] = R.[MatchID]
                    ,[PlayerPersonID] = R.[PlayerPersonID]
                    ,[TeamID] = R.[TeamID]
                    ,[YellowCards] = R.[YellowCards]
                    ,[DoubleYellowCards] = R.[DoubleYellowCards]
                    ,[RedCards] = R.[RedCards]
                    ,[Fouls] = R.[Fouls]
                    ,[Goals] = R.[Goals]
                    ,[PenaltyGoals] = R.[PenaltyGoals]
                    ,[PenaltiesMissed] = R.[PenaltiesMissed]
                    ,[PenaltiesSaved] = R.[PenaltiesSaved]
                    ,[Shots] = R.[Shots]
                    ,[Attacks] = R.[Attacks]
                    ,[Corners] = R.[Corners]
                    ,[Offsides] = R.[Offsides]
                    ,[Assists] = R.[Assists]
                    ,[OwnGoals] = R.[OwnGoals]
                    ,[GoalsConcedeed] = R.[GoalsConcedeed]
                    ,[CreatedBy] = R.[CreatedBy]
                    ,[CreatedOn] = R.[CreatedOn]
                    ,[ModifiedBy] = R.[ModifiedBy]
                    ,[ModifiedOn] = R.[ModifiedOn]

WHEN NOT MATCHED BY SOURCE AND M.ProviderID = 1
    THEN DELETE;

The [Replication].MatchPlayerStatistics table is an intermediate table that is filled with data from the [dbo].MatchPlayerStatistics table on another copy of the database. The schema for all these tables is the same.

Answer 1

One idea I have is to check and see if there are triggers on the table and if one of them is inserting to another table that has a unique index.

Looking at your code, here is a completely wild guess. You are using IGNORE_DUP_KEY = OFF which is normally only used for unique constraints, so I wonder if it is considering it a unique constraint even without the unique keyword. Try creating the index without that phrase and see what happens.