Cannot insert duplicate key row in object with index ncix_id with non-unique index

Question

Cannot insert duplicate key row in object with index ncix_id with non-unique index

Oleksii Filipov 20

Hello!

Microsoft SQL Server 2019 (RTM-CU20) (KB5024276) on Linux (CentOS Linux 7 (Core)) <X64>

We have a table with 3 indexes:

FIX_ActionSerial - nonclustered, unique located on PRIMARY - ActionSerial
ncix_planID - nonclustered located on PRIMARY - PlanId
PK_CampaignSummary - clustered, ignore duplicate keys, unique, primary key located on PRIMARY - MonthId, WeekId, ActionSerial, LifecycleStageId, KpiId

Index number 2 was created recently and immediately after that we started to get an error message:

Cannot write data to SQL 
Cannot insert duplicate key row in object 'mpe.CampaignSummary' 
with unique index 'ncix_planID'. 
The duplicate key value is (794, 94, 404, 39318, 0, 1).\n
The statement has been terminated

Those values from the error message are PlanId from nonclustered non-unique index + all columns from PK. It is completed on a rerun.

Does anybody has an idea:

Why we have ncix_PlanID index in an error message saying it is UNIQUE? (yes I know we still have every index unique behind the scene)
Why we see values like that - yes I know nonclustered index contains clustered key but it should be behind the scene, isn't it?

I would appreciate any thoughts.

P.S. We do not have indexed views on a table.

Thanks!

Olaf Helper 47,436 Reputation points

2024-09-09T11:02:50.5033333+00:00

Please post the CREATE statement for your index ncix_planID

Oleksii Filipov 20

This is how I created it.

I cannot script existing index right now because already removed it.

IF OBJECT_ID('mpe.CampaignSummary') IS NOT NULL
BEGIN
	IF NOT EXISTS (SELECT * FROM sys.indexes WHERE name='ncix_planID' and object_id = OBJECT_ID('mpe.CampaignSummary'))
	SELECT @SQL = 'create nonclustered index ncix_planID on mpe.CampaignSummary(PlanID)'
	EXEC (@SQL)
END

Erland Sommarskog 121.4K Reputation points MVP Volunteer Moderator

2024-09-09T21:18:30.9733333+00:00

So these errors were transient? How often did they appear?

I can't but feel that IGNORE_DUP_KEY is part of the equation. Like two processes inserting the same values in parallel, and there is some timing issue that leads to this error.

Your answer

Olaf Helper 47,436 Reputation points

2024-09-09T11:02:50.5033333+00:00

Please post the CREATE statement for your index ncix_planID
Oleksii Filipov 20 Reputation points

2024-09-09T11:08:56.64+00:00

This is how I created it.

I cannot script existing index right now because already removed it.

IF OBJECT_ID('mpe.CampaignSummary') IS NOT NULL BEGIN IF NOT EXISTS (SELECT * FROM sys.indexes WHERE name='ncix_planID' and object_id = OBJECT_ID('mpe.CampaignSummary')) SELECT @SQL = 'create nonclustered index ncix_planID on mpe.CampaignSummary(PlanID)' EXEC (@SQL) END
Erland Sommarskog 121.4K Reputation points MVP Volunteer Moderator

2024-09-09T21:18:30.9733333+00:00

So these errors were transient? How often did they appear?

I can't but feel that IGNORE_DUP_KEY is part of the equation. Like two processes inserting the same values in parallel, and there is some timing issue that leads to this error.

Share via

Cannot insert duplicate key row in object with index ncix_id with non-unique index

Your answer