Violation of primary key

[Zamdrist]

Violation of PRIMARY KEY constraint 'PK_CUSTOM2'. Cannot insert
duplicate key in object 'MHGROUP.Custom 2'

Is there ANY other reason this violation of the primary key would
happen OTHER than a trying to insert a duplicate record?

This sql statement false due to the primary key violation:

Insert Into MHGROUP.Custom2
Select
ClientNumber,
MatterNumber,
MatterDescripti on,
'Y'
From MG_EliteMatters EM
Left Outer Join MHGROUP.Custom2 C2
On C2.CPARENT_ALIA S = EM.ClientNumber
And C2.Custom_ALIAS = EM.MatterNumber
Where CPARENT_ALIAS Is Null And Custom_ALIAS Is Null

---Custom2
CREATE TABLE [MHGROUP].[CUSTOM2](
[CPARENT_ALIAS] [varchar](32) COLLATE SQL_Latin1_Gene ral_CP1_CI_AS
NOT NULL,
[CUSTOM_ALIAS] [varchar](32) COLLATE SQL_Latin1_Gene ral_CP1_CI_AS NOT
NULL,
[C_DESCRIPT] [varchar](254) COLLATE SQL_Latin1_Gene ral_CP1_CI_AS
NULL,
[ENABLED] [char](1) COLLATE SQL_Latin1_Gene ral_CP1_CI_AS NULL,
CONSTRAINT [PK_CUSTOM2] PRIMARY KEY CLUSTERED
(
[CPARENT_ALIAS] ASC,
[CUSTOM_ALIAS] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]

--MG_EliteMatters
CREATE TABLE [dbo].[MG_EliteMatters](
[Matters] [varchar](16) COLLATE SQL_Latin1_Gene ral_CP1_CI_AS NULL,
[ClientNumber] [varchar](32) COLLATE SQL_Latin1_Gene ral_CP1_CI_AS
NULL,
[ClientDescripti on] [varchar](254) COLLATE
SQL_Latin1_Gene ral_CP1_CI_AS NULL,
[MatterNumber] [varchar](32) COLLATE SQL_Latin1_Gene ral_CP1_CI_AS
NULL,
[MatterDescripti on] [varchar](254) COLLATE
SQL_Latin1_Gene ral_CP1_CI_AS NULL,
[LastDateModifie d] [datetime] NULL,
[PracticeArea] [varchar](50) COLLATE SQL_Latin1_Gene ral_CP1_CI_AS
NULL
) ON [PRIMARY]

[Erland Sommarskog]

Re: Violation of primary key

Zamdrist (zamdrist@gmail .com) writes:

As far as using NOT EXISTS vs. the Left Outer Join syntax...isn't this
more a matter of style than correctness? In a Left Outer Join the two
fields in the destination would be NULL as the source records are not
found in the destination table. I understand NOT EXISTS also works,
but I'm inclined to believe that using JOINs are more efficient.

It's indeed matter of style and expressiveness than correctness.

As for efficient, you can never tell before you benchmark the query at hand.
If the optimizer does it right, you should get the same plan in both cases
anyway.

--
Erland Sommarskog, SQL Server MVP, esquel@sommarsk og.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx