Foreign key and transactional replication
MS SQL Server 2005
Publisher: sdmsrv.siddiqsons
Subscriber: dcsrv.prod_dcity
Setup tables such as colour, customer, product etc are maintained in sdmsrv.siddiqsons and prod_dcity cannot add/edit these tables.
These tables primary keys are foreign keys to many many tables.
We are replicating these tables (transactional replication) to prod_dcity.
Sometimes rather quite oftern we had to regenerate snapshot or re-activate the replication. Then, sql server method is to delete rows (or drop the table completely) in prod_dcity. BECAUSE OF THIS BEHAVIOUR , WE GET FOREIGN KEY ERROR.
RIGHT NOW, WE HAVE REMOVED ALL FOREIGN KEYS IN PROD_DCITY RELATED WITH THE SETUP TABLES BUT THIS SEEMS TO BE VERY BAD.
HOW TO OVERCOME THIS BEHAVIOUR OF SQL SERVER.
Publisher: sdmsrv.siddiqsons
Subscriber: dcsrv.prod_dcity
Setup tables such as colour, customer, product etc are maintained in sdmsrv.siddiqsons and prod_dcity cannot add/edit these tables.
These tables primary keys are foreign keys to many many tables.
We are replicating these tables (transactional replication) to prod_dcity.
Sometimes rather quite oftern we had to regenerate snapshot or re-activate the replication. Then, sql server method is to delete rows (or drop the table completely) in prod_dcity. BECAUSE OF THIS BEHAVIOUR , WE GET FOREIGN KEY ERROR.
RIGHT NOW, WE HAVE REMOVED ALL FOREIGN KEYS IN PROD_DCITY RELATED WITH THE SETUP TABLES BUT THIS SEEMS TO BE VERY BAD.
HOW TO OVERCOME THIS BEHAVIOUR OF SQL SERVER.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Ok.. Enforce For Replication -- Indicates whether to enforce the constraint when a replication agent performs an insert, update, or delete on this table.
<< We have definite sets of colours and it is strictly maintained in main server and users of other servers are not allowed to create new colour. Hence we only replicate colour and off-course then we have the above problem. >>
Then we have to replicate colours table first and after that do the child tables. But what happens if that order is changed.. You would get a violation right and that is what happening to you.
Ideally we have it turned on.. But we need to turn it off in some cases when records we are going to merge violates the Foreign key. A more detailed explanation is provided in the link below for you reference:
http://www.ssw.com.au/SSW/kb/KB.aspx?KBID=Q155706
http://support.microsoft.com/kb/308266
Hope the above two provides more insight and why I asked you to turn if to off.
<< We have definite sets of colours and it is strictly maintained in main server and users of other servers are not allowed to create new colour. Hence we only replicate colour and off-course then we have the above problem. >>
Then we have to replicate colours table first and after that do the child tables. But what happens if that order is changed.. You would get a violation right and that is what happening to you.
Ideally we have it turned on.. But we need to turn it off in some cases when records we are going to merge violates the Foreign key. A more detailed explanation is provided in the link below for you reference:
http://www.ssw.com.au/SSW/kb/KB.aspx?KBID=Q155706
http://support.microsoft.com/kb/308266
Hope the above two provides more insight and why I asked you to turn if to off.
ASKER
Thanks
ASKER
Though the question is accepted,
In the foreign key, there is an option "Enforce for replication" . What does that do.
Our behaviour,
We have definite sets of colours and it is strictly maintained in main server and users of other servers are not allowed to create new colour. Hence we only replicate colour and off-course then we have the above problem.
Please tell, are we doing things in the right way?