Sql-server – Replication fails because of a non-existent foreign key

foreign keyreplicationsql serversql-server-2012

I have been trying to get a previously working replication to start working.

I have tried many settings, but I can't seem to make it work.

Here is the scenario. I dropped all the FK constraints from the destination database (call it DestDB).

I then re-initalize my replication and when I Runs I get this (de-identified) error:

The ALTER TABLE statement conflicted with the FOREIGN KEY constraint
"FK_TableOnlyInDestDB_MyReplicatedTable". The conflict occurred in database
"DestDB", table "SomeSchema.MyReplicatedTable",
column 'MyReplicatedTableId'. (Source: MSSQLServer, Error number: 547)

It is driving me nuts because the FK indicated does not exist in that database.

Now, I did copy this database (backup and restore) from another database. So the only thing I can think of is that it is somehow crossing the streams.

But that seems unlikely.

Any ideas would be great!

NOTE: This is the query I ran to ensure that I have no FKs (It returned no rows):

use DestDB

SELECT f.name AS ForeignKey,
   OBJECT_NAME(f.parent_object_id) AS TableName,
   COL_NAME(fc.parent_object_id, fc.parent_column_id) AS ColumnName,
   OBJECT_NAME (f.referenced_object_id) AS ReferenceTableName,
   COL_NAME(fc.referenced_object_id, fc.referenced_column_id) AS ReferenceColumnName
FROM sys.foreign_keys AS f
INNER JOIN sys.foreign_key_columns AS fc ON f.OBJECT_ID = fc.constraint_object_id

I also checked to ensure that the FK in question was not on the source table (in the database I am replicating from).

I have also tried: EXEC sp_msforeachtable 'ALTER TABLE ? NOCHECK CONSTRAINT all'

Also tried Dropping MyReplicatedTable. Dropped just fine and replication re-created it. (Still gave me the error though.)

I tried creating an empty database as a subscriber. That worked fine, so it is something in the DestDB that is causing the issue, but the FK in the ERROR does not exist.

Best Answer

Sorry for waking up this dead thread, but I hate it when questions remains inconclusive. Anyway, I had the same problem. Apparently SQL server replication saves foreign keys in "dbo.MSsavedforeignkeys", which is where I found my blocker FK. A quick delete dbo.MSsavedforeignkeys where constraint_name = N'FK_TableOnlyInDestDB_MyReplicatedTable' solved my problem. Of course, there may be more "best practice" ways of cleaning up after failed replications.

Related Question