SQL Server Performance Forum – Threads Archive

foreign key constraint error -replication

I’m trying transactional replication between two SQL 2000 servers. I keep on
getting a foreign key constraint error, you can find the error below. Does anybody know how I can work around this problem? "could not drop object my table because it’s referenced by a foreign key constraint" Thanks

option 1
In the destination database drop the foreign key constraint manually option 2
set the articles to truncate the tables rather than drop them You won’t need foreign key constraints in transactional replication unless you want to update the tables on both sides. The error sometimes happens if you drop an article from the publication, but don’t drop the table out of the destination database Cheers
Twan

I think I will use the second option, can you please how to do this?
option 2
set the articles to truncate the tables rather than drop them Thank you..

In EM you can:
open the options for this publications
choose the articles table
either click on default to set all articles to truncate (best to check each article afterwards to make sure that it changed the existing articles)
or for each article click the … button next to it and change the option from drop to truncate. NOTE that this will require the subscription to be reinitialised Does the target database need any fk constraints? It is easiest to drop them if the replication is the only thing that updates those tables Cheers
Twan

I’ve tried dropping the foreign key constraints and it worked, but actually I would prefer to choose the truncate option, but I couldn’t find where that is , by default drop the tables and recreate is selected but I couldn’t see the truncate option.

The option should be on the same screen and is called "delete all data in the existing table" Cheers
Twan

I tried the second option but the replication was not successful. it gives the following error;
could not update identity column testID also another problem that I’m having is, after I drop all the foreign constraints of the destination table, replication works, but only if I replicate only couple of tables, if I decide to replicate about 350 tables then I can’t even update the source table, query runs forever. Do you have any idea why? Thanks..

Hi Ferol, no I’m not sure why off hand… I replicate 100 or so tables here and don’t have that particular problem… On the target database it can sometimes take quite some time if you need to delete all the rows out of tables. In general on the target database you don’t want
– foreign key constraints
– unique constraints
– check constraints
– triggers
– identity columns All of these are only required if you want to update the target database, and all of them make replication more complicated. Also for ease of management set the target database to have the recovery mode "simple" rather than "full", again only if you are not updating the target database Before I reinitialise a large transactional replication, I run a truncate table against every table in the target database. This seems to be much quicker than letting the distribution agent take care of it Cheers
Twan

Hi Twan, I have had great struggle with a simple snapshot replication. I had turned off all "enforce for replication" and "enforce for INSERT and UPDATE"… no go. It was driving me absolutely nuts! Just wanted to let you know, that your second option has worked perfectly! [<img src=’/community/emoticons/emotion-2.gif’ alt=’

‘ />] You have just saved my day… err..my week…err.. Thanks a million, MakeItSo "Never lose your cool – even the most honest finder couldn’t give it back to you…" – Unknown

]]>