violation of PRIMARY KEY constraint (Merge Replication)

Question

violation of PRIMARY KEY constraint (Merge Replication)

gconst70

SSC Veteran

Points: 292
More actions
December 4, 2007 at 5:15 am

#93017

Hi to all!
I have merge replication running, but when I update data from certain tables I get the conflict :
“The row was inserted at 'WDDSQL.ENVIS_merge' but could not be inserted at 'GSD_2.ENVIS_Merged'. Violation of PRIMARY KEY constraint 'aaaaawat_springflow_PK'. Cannot insert duplicate key in object 'wat_springflow'.”
The same happens in few other tables.
In this table the primary key is a combination of two fields and is not an identity.
In a second table that I get the same problem the primary key is alone and again not an identity.
The table 'wat_springflow' has relationship wat_springflow_FK00 set with the following checkboxes on:
Check existing data on creation : not checked
Enforce relationship for INSERTs and UPDATEs : checked
Enforce relationship for replication : checked
Cascade Update Related Fields : checked
Cascade Delete Related Fields : checked
Somehow any changes hapening in relation to those tables the records go into conflict tables
With the message mentioned.
Any help?
Thanks allot.
George

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Chris Becker SSCrazy Points: 2585 More actions · Answer 1

You say you are updating - but the error is an insert - PK violation. Are there triggers on your merge tables? (Other then the merge triggers of course)

If not triggers, most likely you are creating a PK value at 1 server that already exists on the other.

If you are 100% positive this is not the case - look at service packs - do you have latest installed on all servers involved in repl - pubs/distrib/subs

ChrisB MCDBA

MSSQLConsulting.com

Chris Becker bcsdata.net

Graeme100 SSCertifiable Points: 7925 More actions · Answer 2

Hi,

Primary key violation could be the key already exists...in which case are your identity ranges correct...do you need to reseed. Or if you have triggers, do they need to be NOT FOR REPLICATION..

HTH

Graeme

gconst70 SSC Veteran Points: 292 More actions · Answer 3

Hi Chris and thanks for the reply

My sevice packs are:

Standard Edition on Windows NT 5.0 (Build 2195: Service Pack 4)

The only trigger on the table is :

"Alter TRIGGER wat_springflow_record_m_fields_trigger

on wat_springflow FOR UPDATE

AS

IF (COLUMNS_UPDATED() > 0)

UPDATE d

SET record_mn = right(USER,8),

record_md = (getdate())

FROM inserted i

INNER JOIN wat_springflow AS d ON i.site_id = d.site_id and i.result_dt = d.result_dt",

that updates the user name for modification of the record. The fields site_id and result_dt create the primary key.

So propably the case is that I'm creating a PK value at 1 server that already exists on the other, but how can I verify this and avoid it? Where I had an identity I used the not for replication option. But here the pk key is a combination and in the other table that as well causes the message is just the site_id that is pk and they both not an identity. On the tables in this case the site_id value defaults to (0). Both fields are included in my replication and for sure they exist on the other server. Should I just not include them in replication? or is there any other scheme?

This happens when the user modifies some values in the ms access aplication so it must be an update case.

I hope I gave you the picture.

Thanks allot

George

gconst70 SSC Veteran Points: 292 More actions · Answer 4

Hi Graeme

and thanks for the reply.

Thats what I'm thinking as well with Chris answer and your verification. In my case the primary key is not set as an identity so I cannot use the "not for replication" option on the table and fix the seeds. Is there a way to do that?

The primary key values for sure they exist on both tables, but Im not using the not for replication option because they are not set as an identity. Should I set them up and an identity and use the not for replication option? But how would I do that for the combine fields primary Key?

Thanks

George

Graeme100 SSCertifiable Points: 7925 More actions · Answer 5

Hi George,

I think I need to see your table structure.

I think a composite PK is getting a little complicated, it is far easier to manage with identities...I've found anyway 🙂

Regards

Graeme

gconst70 SSC Veteran Points: 292 More actions · Answer 6

Hi there:

This is a snapshot of the script generated by sql server 2000

Thanks

George

/****** Object: Trigger dbo.wat_springflow_record_m_fields_trigger Script Date: 05/12/2007 14:41:17 ******/

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[wat_springflow_record_m_fields_trigger]') and OBJECTPROPERTY(id, N'IsTrigger') = 1)

drop trigger [dbo].[wat_springflow_record_m_fields_trigger]

GO

/****** Object: Table [dbo].[wat_springflow] Script Date: 05/12/2007 14:41:17 ******/

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[wat_springflow]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)

drop table [dbo].[wat_springflow]

GO

/****** Object: Table [dbo].[wat_springflow] Script Date: 05/12/2007 14:41:22 ******/

CREATE TABLE [dbo].[wat_springflow] (

[site_id] [int] NOT NULL ,

[result_dt] [datetime] NOT NULL ,

[result_va] [float] NULL ,

[method_cd] [smallint] NULL ,

[remark_tx] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,

[record_cn] [varchar] (8) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,

[record_cr] [datetime] NOT NULL ,

[record_mn] [varchar] (8) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,

[record_md] [datetime] NULL ,

[rowguid] uniqueidentifier ROWGUIDCOL NOT NULL

) ON [PRIMARY]

GO

ALTER TABLE [dbo].[wat_springflow] WITH NOCHECK ADD

CONSTRAINT [DF_wat_springflow_site_id] DEFAULT (0) FOR [site_id],

CONSTRAINT [DF_wat_springflow_record_cn] DEFAULT (right(user_name(),8)) FOR [record_cn],

CONSTRAINT [DF_wat_springflow_record_cr] DEFAULT (getdate()) FOR [record_cr],

CONSTRAINT [DF__wat_sprin__rowgu__62D6BD83] DEFAULT (newid()) FOR [rowguid],

CONSTRAINT [aaaaawat_springflow_PK] PRIMARY KEY NONCLUSTERED

(

[site_id],

[result_dt]

) WITH FILLFACTOR = 90 ON [PRIMARY]

GO

CREATE INDEX [wat_spring_geographywat_springflow] ON [dbo].[wat_springflow]([site_id]) WITH FILLFACTOR = 90 ON [PRIMARY]

GO

ALTER TABLE [dbo].[wat_springflow] ADD

CONSTRAINT [wat_springflow_FK00] FOREIGN KEY

(

[site_id]

) REFERENCES [dbo].[wat_spring_geography] (

[site_id]

) ON DELETE CASCADE ON UPDATE CASCADE

GO

/****** Object: Trigger dbo.wat_springflow_record_m_fields_trigger Script Date: 05/12/2007 14:41:25 ******/

CREATE TRIGGER wat_springflow_record_m_fields_trigger