Foreign key and transactional replication
Posted on 2009-02-16
MS SQL Server 2005
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.