Snapshot Repl. Failing with Foreign Key Constraints

Question

Snapshot Repl. Failing with Foreign Key Constraints

Ian Leach

SSC Veteran

Points: 218
More actions
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.

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

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

Hilary Cotter SSCommitted Points: 1861 More actions · Answer 1

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

Jules Bui SSC Eights! Points: 837 More actions · Answer 2

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

Ian Leach SSC Veteran Points: 218 More actions · Answer 3

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.

Jules Bui SSC Eights! Points: 837 More actions · Answer 4

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

K.Senthil Kumar-258318 Valued Member Points: 55 More actions · Answer 5

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

Jules Bui-131038 SSC Enthusiast Points: 198 More actions · Answer 6

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

Ian Leach SSC Veteran Points: 218 More actions · Answer 7

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

Ian Leach SSC Veteran Points: 218 More actions · Answer 8

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

Opinions wanted!

Jules Bui SSC Eights! Points: 837 More actions · Answer 9

Yes, I believe transactional does push schema changes also.

Jules Bui
IT Operations DBA
Backup and Restore Administrator

Graeme100 SSCrazy Eights Points: 8054 More actions · Answer 10

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

Jules Bui SSC Eights! Points: 837 More actions · Answer 11

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://www.sqlservercentral.com/scripts/contributions/1556.asp

Jules

Jules Bui
IT Operations DBA
Backup and Restore Administrator