合并复制期间违反PRIMARY KEY约束 [英] Violation of PRIMARY KEY constraint during Merge Replication

问题描述

我有两个相同的SQL Server 2005数据库(MYDB_Pub，MYDB_Sub1)，在该数据库中配置了合并复制，并且工作正常.

I have two identical SQL Server 2005 databases (MYDB_Pub, MYDB_Sub1) where the merge replication was configured and it works fine.

最近，我们升级到了SQL Server2014.为了测试新SQL Server上的复制功能，我遵循以下步骤:

Recently we upgraded to SQL Server 2014. To test the replication functionality on the new SQL Server I followed the below steps:

• 在SQL Server 2005上备份MYDB_Pub.
• 使用相同的名称在SQL Server 2014上还原MYDB_Pub.
• 使用名称在SQL Server 2014上恢复相同的MYDB_Pub "MYDB_Sub1".
• 在单个表国家/地区"上配置合并复制.

• Backup the MYDB_Pub on the SQL Server 2005.
• Restore the MYDB_Pub on the SQL Server 2014 with the same name.
• Restore the same MYDB_Pub on the SQL Server 2014 with name 'MYDB_Sub1'.
• Configure Merge Replication on a single table 'Country'.

现在，当我运行复制过程时，出现以下错误消息:

Now when I run the replication process, I get the following error message:

表结构如下:

CREATE TABLE [dbo].[Country](
    [CountryId] [int] IDENTITY(1,1) NOT FOR REPLICATION NOT NULL,
    [Code] [char](2) NOT NULL,  
    [CountryName] [varchar](50) NOT NULL,   
    [rowguid] [uniqueidentifier] ROWGUIDCOL  NOT NULL CONSTRAINT [DF_Country_rowguid]  DEFAULT (newsequentialid()) 
)
GO
ALTER TABLE [dbo].[Country]  WITH NOCHECK ADD  CONSTRAINT [repl_identity_range_BEB70305_9154_4BBE_B898_61681A047BA2] CHECK NOT FOR REPLICATION (([CountryId]>(112251) AND [CountryId]<=(113251) OR [CountryId]>(113251) AND [CountryId]<=(114251)))
GO
ALTER TABLE [dbo].[Country] CHECK CONSTRAINT [repl_identity_range_BEB70305_9154_4BBE_B898_61681A047BA2]
GO

请注意，在定义复制之前，数据库中已经存在[rowguid]列.发布者和订阅者中的Country表具有相似的数据，因为发布者和订阅者是从同一文件中还原的.

Please note that [rowguid] column was already there in the database before defining the replication. Country table in both publisher and subscriber have similar data as publisher and subscriber were restored from same file.

要配置复制，我使用了这篇文章.

To configure the replication I used the instruction provided in this article.

Country表的订阅属性和文章属性显示在以下屏幕截图中

Subscription properties and Article properties of Country table are shown in the below screenshots

我试图找到解决此错误的方法，但没有任何帮助.我必须明确指出我不是DBA，并且这是我第一次在SQL Server上使用复制.我将非常感谢您的帮助.

I have tried to find the solution for this error but nothing helped. I must make it clear that I am not a DBA and its the first time I am playing with replication on SQL Server. I would really appreciate any help.

推荐答案

出现此错误的原因是因为该表已存在于订阅服务器上，并且已被填充，并且初始化尝试使用以下命令重新填充该表来自发布者的数据.这样做是因为您的文章属性显示如果正在使用名称，请执行操作=保持现有对象不变".我相信默认值是删除表并重新创建对象"或类似的东西，这意味着在同步初始化时，它将在订户处删除该表，然后填充它.这样可以避免上面的错误，因为订阅服务器上的表在填充之前将为空.

The reason you are getting the error is because the table already exists on the subscriber, and is populated, and the initialization is trying to re-populate the table with the data from the publisher. It is doing this because your article property says "Action if name is in use = Keep existing object unchanged". I believe the default is "drop table and recreate object" or something like that, which means when you synchronize the initialization, it will drop the table at the subscriber, then populate it. This would avoid the error above, since the table at the subscriber will be empty before it is populated.

因此，您需要确定，是否需要订阅服务器上的现有数据，或者是否在初始化期间，可以让合并复制删除对象，然后根据发布服务器的快照重新填充对象.

So you need to decide, if you need the existing data at the subscriber, or if during initialization, you can let merge replication drop the objects, and repopulate it based off the snapshot from the publisher.

这篇关于合并复制期间违反PRIMARY KEY约束的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！