[CR# 583437] Incorrect LTL may be generated in a partition schema for an update transaction that affects the primary-key column of a table referenced by the cascading foreign-key column of another table. For example, two tables are created, one with a primary-key column referenced by the foreign-key column of the other table. This causes updates to cascade to the primary-key column of the first table:
create table table1 (id int constraint PK_1 PRIMARY KEY CLUSTERED WITH FILLFACTOR=90 on myRangePS1(id), value1 varchar(8) null);create table table2 (id int constraint FK_1 FOREIGN KEY REFERENCES table1(id) ON UPDATE CASCADE, value1 varchar(8) null);
Data is inserted into both tables:
insert into table1 values(3,'aaa'); insert into table2 values(3,'aaa');
The primary-key column of the first table is subsequently updated:
update table1 set id =4
The resulting update transaction results in the following logged commands:
LOP_BEGIN_XACT NULL LOP_BEGIN_UPDATE NULL LOP_DELETE_ROWS dbo.table1.PK_1 LOP_INSERT_ROWS dbo.table1.PK_1 LOP_DELETE_ROWS dbo.table2 LOP_INSERT_ROWS dbo.table2 LOP_END_UPDATE NULL LOP_COMMIT_XACT NULL
The LTL generated for this transaction does not identify that the delete operations are grouped with insert operations as part of an overall update operation. Consequently, Replication Server is suspended.
Workaround: Drop the foreign key constraint in your replicate database. This will not result in data loss because both the original delete and the cascade delete commands are sent to the replicate database.
