December 7, 2007 at 10:37 pm
I have implemented transactional replication between two databases in SQL Server 2000. Both databases are exactly the same; because of business rule CASCADE DELETE is not allowed.
I faced with this problem:
When the primary Key has been updated Log reader change the UPDATE with pair of DELETE and INSERT statements. Therefore it conflicts with relationships and not allowed cascade delete among tables.
How can I solve this problem?
December 10, 2007 at 10:54 pm
Remove FK's at subscriber. Data integrity is controlled at publisher.
ChrisB MCDBA
MSSQLConsulting.com
Chris Becker bcsdata.net
January 14, 2008 at 1:14 am
Hmmm...tricky one.
By design, if you update a unique constraint in replication, the replicated command becomes a pair i.e. an delete/insert.
Unless you have a composite unique constraint and are updating one part of that, why would you update a single primary key....(not sure you are of course)
The pair of commands should be fine if they are just being applied to the subscriber..do you have triggers that are passing the data to another database...if so you may need to change the business logic of these triggers.
HTH
Graeme
January 14, 2008 at 1:16 am
Sorry..forgot to mention.
You could have your FKs not for replication, that way they will ignore the violation caused by the replicated commands.
My question would then be, why do you need FKs on your subscriber (unless it is an updating subscriber (TRANS REP))
Graeme
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply