By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
455,378 Members | 1,455 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 455,378 IT Pros & Developers. It's quick & easy.

Violation of primary key

P: n/a
Violation of PRIMARY KEY constraint 'PK_CUSTOM2'. Cannot insert
duplicate key in object 'MHGROUP.Custom2'

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,
MatterDescription,
'Y'
From MG_EliteMatters EM
Left Outer Join MHGROUP.Custom2 C2
On C2.CPARENT_ALIAS = 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_General_CP1_CI_AS
NOT NULL,
[CUSTOM_ALIAS] [varchar](32) COLLATE SQL_Latin1_General_CP1_CI_AS NOT
NULL,
[C_DESCRIPT] [varchar](254) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL,
[ENABLED] [char](1) COLLATE SQL_Latin1_General_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_General_CP1_CI_AS NULL,
[ClientNumber] [varchar](32) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL,
[ClientDescription] [varchar](254) COLLATE
SQL_Latin1_General_CP1_CI_AS NULL,
[MatterNumber] [varchar](32) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL,
[MatterDescription] [varchar](254) COLLATE
SQL_Latin1_General_CP1_CI_AS NULL,
[LastDateModified] [datetime] NULL,
[PracticeArea] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL
) ON [PRIMARY]
Mar 28 '08 #1
Share this Question
Share on Google+
1 Reply


P: n/a
Zamdrist (za******@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, es****@sommarskog.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
Mar 30 '08 #2

This discussion thread is closed

Replies have been disabled for this discussion.