January 16, 2008 at 5:09 am
Hi
Ive got a SQL 2005 database called MEL which has a copy on each of two servers SQL28 and SQL94.
The table CustomerLevel is published on SQL28, SQL94 subscribes to it (transactional replication, updates to subscriptions not allowed).
There is a table called FixedCharge on SQL94.
I created a Foreign Key Constraint on SQL94 FixedCharge that references SQL94 CustomerLevel. Im aware that I need to recreate this FK each time I re-intialise replication which is not ideal, I know.
However, the replication logreader agent has reported that this constraint is broken, even when I know that there has not been a deletion of a CustomerLevel Record on SQL28 that I would expect to break the constraint.
Is it because when an update to a CustomerLevel record on SQL28 is made, SS Replication is actually doing a DELETE and INSERT of the record, rather than doing the UPDATE which I would expect the constraint to hold for if the two tables were in the same database?
Any ideas or workarounds would be greatly appreciated, I know the design of the system is not ideal.
Cheers
Rich
January 16, 2008 at 5:59 am
Hi richard,
"Im aware that I need to recreate this FK each time I re-intialise replication" you stated is not true. The process you went through to create your publication will in fact allow you to publish foriegn keys of those table by configuring the properties of the tables in your publication.
Also note that if you choose to publish those table along with the foriegn key, all tables to which they are related to have to be published also for the foriegn keys to appear on the subscriber.
hope this help
:):)
January 16, 2008 at 6:09 am
Thanks for the reply,
FixedCharge does not and cannot exist on the publishing database in this case.
It is a static table on SQL094 and not part of a subscription.
So I think maybe I would need to reintialise FKs always after re-initialising replication.
January 17, 2008 at 4:36 am
Does anyone know the answer to my original question -
Does SS Replication is do a DELETE and INSERT of the record, rather than doing the UPDATE which I would expect the constraint to hold for if the two tables were in the same database?
January 17, 2008 at 7:52 am
Do your foreign keys have the "not for replication" element? It could be that the child table entry is being made before the parent table and hence the FK violation.
January 18, 2008 at 3:44 am
Adicttional question to this topic.
When i reinitialize my subscriber with new snapshot in a transactional publication, i drop the foreign keys and apply the snapshot and finally create again the foreign keys.
There is any other process that avoid this procedure??
Thanks.
January 21, 2008 at 2:31 am
Animal Magic,
There is no chance that a fixedcharge (child) record could be created before the Customerlevel(parent) record in this instance as the application logic prohibits it.
I will look in to that NOT FOR REPLICATION option and let you know if this resolves my problem.
Cheers
January 21, 2008 at 3:31 am
Its not that there is any problem with your application logic, the primary database will be fine, its the replicated database. Even if the 2 tables (parent and child) are in the same publication sql still sometimes applies the rows in the wrong order, even though its not meant too, its meant to maintain the integrity of the data.
Adding the not for replication element to the foreign keys gets around this problem.
January 21, 2008 at 7:14 am
what u suggest??
use not for replication foreign keys or the default way?
January 21, 2008 at 7:21 am
I would generally use the not for replication option on foreign keys in a replication scenario.
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply