Unanswered: replicating and update as a delete-insert
I have a weird problem that has seemingly popped up all of a sudden, and on multiple servers I have set up as publishers. All servers involved are running SQL Server 7.0.
Basically, the user will perform an UPDATE command on a parent table, but when the command is distributed to the subscriber, it is sent as a DELETE followed by an INSERT. Since I have foreign key constraints at both the publisher and subscriber, the replication fails with a key violation, since it tries to delete a record with children.
I have checked to ensure that the key constraints are the same at both places, I have even dropped and recreated them all.
The only thing that I can think of that has changed is that one of the new DBAs has Enterprise Manager for SQL 2000 installed on their workstation, and has been monitoring the replication with it. The publications have all been defined and running normally for several months without these problems, and that's the only change I can think of.
Originally posted by joejcheng
To resolve this problem, simply change your foreign key constraint to "Not for Replication".
That did it. Thanks a ton Joe.
I also figured out why it was happening, and I don't really like what I found.
I have a unique constraint defined on smalldatetime field and userID field in my offending table, and apparently one of them is getting updated when my error occurs. According to the ms knowledge base, if you update a column that is part of a unique constraint, is will execute the update as a 'deferred update' which really is a DELETE/INSERT pair instead of an update. At the publisher, the deferred update is treated like a regular update, meaning it wont break fkey constraints and it will still use the UPDATE trigger and not the DELETE-INSERT triggers, but the DELETE/INSERT pair are read by the logreader and go into the distribution database instead of the UPDATE. It sounds like this 'deferred update' stuff is a bit of a hack to me. It sounds like their unique constraint checking implementation is flawed, and this was a way around it. It could really cause some obscure problems if you had an UPDATE trigger at a subscriber that did something important. If I send an UPDATE command, I'd expect that same command to be replicated as is, regardless of how it is internally executed. Anyway....
If any column that is part of a unique constraint is updated, then SQL Server implements the update as a "deferred update", which means as a pair of DELETE/INSERT operations. This "deferred update" causes replication to send a pair of DELETE/INSERT statements to the subscribers. There are also other situations that might cause a deferred update. Therefore, any business logic that you implement in your UPDATE triggers or custom stored procedures at the Subscriber should also be included in the DELETE/INSERT triggers or custom stored procedures.