Wednesday, April 13, 2011

Replicate table with foreign key

Replicate table with foreign key having "ON DELETE CASCADE".
ALTER TABLE T2 WITH CHECK ADD CONSTRAINT [FK_T2_PKT1] FOREIGN KEY(PKT1)REFERENCES T1 (PKT1)ON

Replicte T1 and T2.

Delete from T1 where PKT1 = 1

On distributor, the following commands will be distributed:
Delete from T1 where PKT1 = 1
Delete from T2 where PKT2 = 2 --maybe more commands for all records in T2 have the foreign key of PKT1

It will work perfect.

But if I create a stored procedure(usp_delete_T1) to delete records in table T1, and replicate the execution of the SP.
exec usp_delete_T1 @PKT1 = 1 on publisher, it will delete records from T1 and T2.

The distributor will send:
 exec usp_delete_T1 @PKT1 = 1 to subscriber.

The problem is only the record in T1 will be deleted from T1. The records in T2 will not be deleted.
So we cannot replicate the execution of usp_delete_T1.

No comments:

Post a Comment