I have several unique indexes. For example
SET ansi_nulls ON
go
SET quoted_identifier ON
go
CREATE TABLE [dbo].[tblrelatedwords]
(
[cl_orgwordid] [BIGINT] NOT NULL,
[cl_relatedwordid] [BIGINT] NOT NULL,
[cl_relatedwordtypecode] [SMALLINT] NOT NULL,
[cl_relation_sourceid] [TINYINT] NOT NULL,
CONSTRAINT [PK_tblSeeAlso] PRIMARY KEY CLUSTERED ( [cl_orgwordid] ASC,
[cl_relatedwordid] ASC, [cl_relatedwordtypecode] ASC )WITH (pad_index = OFF
, statistics_norecompute = OFF, ignore_dup_key = OFF, allow_row_locks = on,
allow_page_locks = on, FILLFACTOR = 90) ON [PRIMARY]
)
ON [PRIMARY]
go
ALTER TABLE [dbo].[tblrelatedwords]
ADD CONSTRAINT [DF_tblSeeAlso_cl_RelatedWordTypeCode] DEFAULT ((255)) FOR
[cl_RelatedWordTypeCode]
go
When i use below query systematic even though i check with If not exists, i am still getting the below error
IF NOT EXISTS
( SELECT 1
FROM tblRelatedWords
WHERE (cl_OrgWordId=@cl_OrgWordId
AND cl_RelatedWordId=@cl_RelatedWordId
AND cl_RelatedWordTypeCode=@cl_RelatedWordTypeCode)
OR (cl_OrgWordId=@cl_RelatedWordId
AND cl_RelatedWordId=@cl_OrgWordId
AND cl_RelatedWordTypeCode=@cl_RelatedWordTypeCode) ) BEGIN
INSERT INTO tblRelatedWords
VALUES (@cl_OrgWordId,
@cl_RelatedWordId,
@cl_RelatedWordTypeCode,
@cl_Relation_SourceId) END
Error
Violation of PRIMARY KEY constraint 'PK_tblSeeAlso'. Cannot insert duplicate key in object 'dbo.tblRelatedWords'. The duplicate key value is (11439364, 2495501, 243). The statement has been terminated.
Yes multiple threads are adding to the same table however aren't IF NOT EXISTS supposed to prevent such cases?
Microsoft SQL Server 2016 (SP1-CU2) (KB4013106) - 13.0.4422.0 (X64)
