Post reply

Snapshot Repl. Failing with Foreign Key Constraints

  • August 30, 2005 at 1:58 am

    #90645

    Hi Guys,

    I wondered if you would be able to assist on this one....?

    I have a server with several publications, all going to databases on one subscriber at a remote site. Some of the DBs work fine with snapshot replication and need no further attention. However, several of the DBs fail out with the following error:

    3726: Could not drop object "TBL NAME" because it is referenced by a foreign key constraint.

    In the publication properties, articles tab, I have set the article defaults for the tables NOT to Include declared referential integrity and have changed the name conflicts to delete all data in the existing table. (I changed from DROP the existing table and recreate it). When I do this I get the following:

    4712: Cannot truncate table "TBL NAME" because it is being referenced by a foreign key constraint.

    I have been chasing my tail on this for a while now and am gradually getting dizzy in the process!!!!

    Any help you could offer would be very much appreciated....

    Cheers,

    Ian.

     

  • August 31, 2005 at 4:09 am

    #586395

    can you post the schema for the problem table? Script it and related tables out along with pk's and fk's on the publisher and subscriber.

    --

    Hilary Cotter

    Looking for a SQL Server replication book?

    http://www.nwsu.com/0974973602.html

     
    Looking for a FAQ on Indexing Services/SQL FTS

    http://www.indexserverfaq.com

  • September 1, 2005 at 1:10 pm

    #586991

    Ian,

    You will need to drop the foreign key constraint before you can drop or truncate any tables that it references to.  You can always add the constraint back after your data is updated.

    Hope this helps

    Jules

    Jules Bui
    IT Operations DBA
    Backup and Restore Administrator

  • September 6, 2005 at 3:38 am

    #587704

    Jules,

     

    Please forgive the somewhat basic nature of my question but I'm not a seasoned SQL Server admin. Still on the learning curve.

    Please can you explain the way I can achieve this?

    Bear in mind I am running production db straight to dr site standby server. Is this something that needs to be done on the prod side and if so, will this affect ref. integrity?

    Ian.

  • September 6, 2005 at 2:11 pm

    #587915

    Ian,

    Sorry I have not had the time today to respond to you today.  The way referential constraints work is through a parent table and one or more mulitiple child tables.  A good example to explain this: Parent table is the Products table in a small grocery store.  Child table would be Invoices.  Referential integrity exists in these two tables because you have to make sure you're selling products that exist in your store.  The foreign key would be on the ProductId column in the Invoices table.  Your replication does not allow you to truncate the parent table because it is being referenced by the child table.  Doing this would give you a table full of orphans.  The way to work around this is to drop the foreign key contraint for the column productid between these two tables, truncate the parent table and refresh it with new data.  After you do this, you can rebuild your foreign key constraint on the productid between the two tables. This will add any new products you have that you might be selling with your new invoices.  Please do this on a test environment 1st.  Hope this helps.

    Jules

    Jules Bui
    IT Operations DBA
    Backup and Restore Administrator

  • September 6, 2005 at 11:30 pm

    #587996

    Me too faced that problem.. But is there any other way to truncate the table without removing the constaints. Because, my table related with many tables...

     

    -Sensoft2000

  • September 7, 2005 at 7:31 am

    #588078

    I think going to transactional replication would be the solution on this if you cannot truncate the tables due to many foreign key constraints.  What do you gurus out there think?

    Jules

  • September 8, 2005 at 12:12 pm

    #588496

    Thanks for the info and your time Jules. This is an interesting one and would love to hear the opinions of the gurus too!!!

    Ian

  • September 8, 2005 at 12:14 pm

    #588497

    BTW - the databases have been initialised at the subscriber-end and contain current schema. Would transactional replication do the job?

    Opinions wanted!

  • September 8, 2005 at 12:58 pm

    #588520

    Yes, I believe transactional does push schema changes also.

    Jules Bui
    IT Operations DBA
    Backup and Restore Administrator

  • September 13, 2005 at 8:00 am

    #589367

    Under the SnapShot tab you can run scripts before and after applying the snapshot. You can always run scripts that drop the FKs before the snapshot is run and put them back after.

    If you reinitialize a subscription, you will need to re-run the snapshot agent which creates a new snapshot of schema and data. This is then distributed via the agent.

    HTH Graeme

  • September 13, 2005 at 9:05 am

    #589402

    Ian,

    Looking back at past articles on this site, found an article that you might be interested in where you can disable foreign keys for snapshot replication.  Here is the link:

    http://qa.sqlservercentral.com/scripts/contributions/1556.asp

     

    Jules

    Jules Bui
    IT Operations DBA
    Backup and Restore Administrator

Viewing 12 posts - 1 through 11 (of 11 total)

You must be logged in to reply to this topic. Login to reply