0

One of my transaction replication started to throw error:

The ALTER TABLE statement conflicted with the FOREIGN KEY constraint "FK_Name". The conflict occurred in database "Database", table "dbo.table", column 'ID'. (Source: MSSQLServer, Error number: 547)

I have dropped the foreign key constraint from both the publisher and the subscriber, created a new snapshot and reinitialized the replication, but I still getting the same error.

After some searches I found that there are three tables, created by the replication (on the subscriber):

dbo.MSsavedforeignkeys
dbo.MSsavedforeignkeycolumns
dbo.MSsavedforeignkeyextendedproperties

and it two of those table, I saw rows with reference to the deleted FK.
I delete the rows from those tables but I still get the errors.

Any idea how to fix this?

Andre Silva
  • 4,782
  • 9
  • 52
  • 65
user2993562
  • 51
  • 1
  • 7

1 Answers1

0

I do not suggest altering the system tables, as they are highly dependent on one another. Chances are, based on your actions you're beyond repair.

If the error isn't happening on the publisher (before you dropped the constraints), then it stands to reason, someone has deleted from the subscriber table... If you can identify the missing row, you can re-insert it on the subscriber, and replication may fix itself.

At this point, it may simply be easier, cleaner, and faster to delete the subscription, and re-build it.

Dave C
  • 7,272
  • 1
  • 19
  • 30