SQL Server Performance

foreign key constraint error -replication

Discussion in 'Performance Tuning for SQL Server Replication' started by ferol, Jan 26, 2004.

  1. ferol New Member

    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"


  2. Twan New Member

    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

  3. ferol New Member

    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..

  4. Twan New Member

    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

  5. ferol New Member

    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.
  6. Twan New Member

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

  7. ferol New Member

    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?

  8. Twan New Member

    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

  9. MakeItSo New Member

    Hi Twan,<br /><br />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.<br />It was driving me absolutely nuts!<br /><br />Just wanted to let you know, that your second option has worked perfectly! [<img src='/community/emoticons/emotion-2.gif' alt=':D' />]<br />You have just saved my day... err..my week...err..<br /><br />Thanks a million,<br />MakeItSo<br /><br />"Never lose your cool - even the most honest finder couldn't give it back to you..." - Unknown

Share This Page