duplicate key error - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Import/Export (DTS) and Replication (2000)

duplicate key error

Author

Topic

Hommer
Aged Yak Warrior

808 Posts

Posted - 2004-08-17 : 12:20:07

It is so common that I have seen it many times, and also have solved it many times, but not this one.

The err says:
Violation of PK constraint 'PK_OrderApprovalSta_3E52440B'. Cannot insert duplicate key in object OrderApprovalStatusHistory.

Here is the table script:
CREATE TABLE [dbo].[OrderApprovalStatusHistory] (
[ApprovalStatusID] [varchar] (50) ,
[ApprovalStatusCode] [varchar] (2),
[head_order_nbr] [varchar] (20),
[ApprovalStatusChangeDate] [datetime] NOT NULL ,
[ChangedByUserId] [varchar] (50),
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[OrderApprovalStatusHistory] ADD
CONSTRAINT [DF__OrderAppr__Appro__3F466844] DEFAULT ('cast(newid() as varchar(50))') FOR [ApprovalStatusID],
CONSTRAINT [DF__OrderAppr__Appro__403A8C7D] DEFAULT (getdate()) FOR [ApprovalStatusChangeDate],
PRIMARY KEY CLUSTERED
(
[ApprovalStatusID]
) ON [PRIMARY]
GO

My problem is that I could not find any violation.
I did not see any PK called PK_OrderApprovalSta_3E52440B either here or any other related tables.
As I understand, the error means that this is a child table and the dts tried to insert a value into its fk column while the parent table doesn't have a value in the PK.
The row number points to the last row in the table. Its Fk value does have an entry in parent table.
Actually there are none orphaned records found.

Should I drop the Alter table part and load the data then re-apply it?

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-08-17 : 12:22:19

What is the DTS doing? Moving data from one table to the next?

Tara

Hommer
Aged Yak Warrior

808 Posts

Posted - 2004-08-17 : 12:50:20

The DTS moves data from Sybase to sql 2000.
On the original table in Sybase, it used three columns as composite PK. The vendor seggests using a newid() to create a PK as replacement.
I think that is the part that caused the problem.

DustinMichaels
Constraint Violating Yak Guru

464 Posts

Posted - 2004-08-17 : 12:53:47

Could you possibly use an identity key for the primary key?
Perhaps this would fix your problem

Dustin Michaels

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-08-17 : 12:58:02

newid() as the PK? That's absurd. newid() could possibly (although unlikely) return duplicates. Why not keep the 3 column composite PK? Or an Identity as Dustin suggested?

Tara

Hommer
Aged Yak Warrior

808 Posts

Posted - 2004-08-17 : 13:21:54

Ok, I will make the change as suggested.

Thanks! :)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources