:::: MENU ::::

The ALTER TABLE statement conflicted with the FOREIGN KEY constraint (Replication error)

  • Mar 24 / 2014
  • 2
dbDigger, Replication, SQL Server Error messages

The ALTER TABLE statement conflicted with the FOREIGN KEY constraint (Replication error)

We had configured transnational replication on a QA server. Publisher and subscriber were on same server. While implementing snapshot we were facing following error message as in replication monitor.

Command attempted:
if object_id(N’sys.sp_MSrestoresavedforeignkeys’, ‘P’) < 0 exec sys.sp_MSrestoresavedforeignkeys
(Transaction sequence number: 0x000B0F2B0002BB5101C300000000, Command ID: 1)

Error messages:
The ALTER TABLE statement conflicted with the FOREIGN KEY constraint “FK_ContractID2”. The conflict occurred in database “%”, table “%”, column ‘%’. (Source: MSSQLServer, Error number: 547)
Get help: http://help/547
The ALTER TABLE statement conflicted with the FOREIGN KEY constraint “FK_ContractID2”. The conflict occurred in database “%”, table “%”, column ‘%’. (Source: MSSQLServer, Error number: 547)
Get help: http://help/547

Subscriber database had no constraint that was mentioned in the error message. Even publisher did not have this constraint. It was a mess that we were not able to find the constraint but it was causing failure to snapshot implementation.

Towards Solution

While looking into details i came across following there tables that were being created by replication processes and contain the foreign key data of replication.

  • dbo.MSsavedforeignkeys
  • dbo.MSsavedforeignkeycolumns
  • dbo.MSsavedforeignkeyextendedproperties

I noticed that ghost enteries for our culprit foreign key were there in these tables. I deleted those entries manually and regenerated the snapshot. This time it was successfully generated and implemented.

  • Thanks, It helped me a lot !
    One question, how did you pointed out the query that was causing the error ?

    Thanks and Regards,
    Avijeet

  • Atif Shehzad

    It was displayed in replication monitor while implementing snapshot.

Consult us to explore the Databases. Contact us