I was hoping someone could explain how cascading deletes work in Peer to Peer replication on SQL 2005 SP2, the child tables are also being replicated.
Did some of my own testing and here's the results:
Peer to Peer Replication Testing | | | |
| | | | |
FK Cascading deletes | | | |
| | | | |
Test 1 | | | |
1 | Create 2 tables with a foreign key dependency, both tables are being replicated. | | |
2 | Create FK relationship with the various option below. | | |
| | | | |
| FK Options | Behaviour | | |
| | SQL1 | SQL2 | Note |
| FOR REPPLICATION | Rows are removed as expected | All rows are delete as with SQL1, not errors are generated. | |
| | | | |
| NOT FOR REPLICATION | Rows are removed as expected | All rows are delete as with SQL1, not errors are generated. | |
| | | | |
| | | | |
Test 2 | | | |
1 | Create 2 tables with a foreign key dependency, only 1 table is replicated replicated. | | |
2 | Create FK relationship with the various option below. | | |
| | | | |
| | | | |
| FK Options | Behaviour | | |
| | SQL1 | SQL2 | Note |
| FOR REPPLICATION | Rows are removed as expected | All rows are delete as with SQL1, not errors are generated. | |
| | | | |
| NOT FOR REPLICATION | Rows are removed as expected | The cascading delete failed, only the primary record was removed. | This does not break replication as the table that retained the row is not replicated. |
No comments:
Post a Comment