MS SQL Server :: Merge Replication Foreign Key Problem

Merge Replication - Foreign Keys

Jul 9, 2004

I am currently new to Merge Replication and have not been able to find one really simple underlying answer to the fundamentals of Merge Replciation....

Foreign Keys.... An example - I have two DBs in separate locations both adding new records. Assume that adding these new records create foreign key links to other tables. Of course when you come to synchronise - a foreign key in a table perhaps now relates to a different record.. How do you deal with this?

Eg: two DBs, the DBs both started out the same with 10 records in the CARS table for example. The two dbs also had 20 records in the MODEL table which states the model name and a foreign key relationship to the cars table. As the two DBs get updated separately and the result is now one DB has 2 new records in the car table for example and the other has 3 new records in its cars table... Of course these have corresponding entries in their MODEL table referencing primary keys. So how do I, using merge replication keep all these new records and update their foreign key references to suit as they synchronise?

It seems fundamental to merge replication and I cannot find advice anywhere on how to do this. Do I have to change both tables to use GUIDs as primary keys rather that incrementing numbers perhaps??

advice would be greatly appreciated

View 8 Replies View Related

Merge Replication And Foreign Keys

Apr 10, 2007

i'm struggling with merge replication and foreign key constraints.

i have four tables:

- owners(id, name, firstname, address, phone)
- cars(id, licenceplate, ownerid) (thus with ownerid FK to the owners table)
- busses(id, licenceplate, ownerid) (thus with ownerid FK to the owners table)
- ships(id, licenceplate, ownerid) (thus with ownerid FK to the owners table)

now, i want to merge only the OWNERS table (article) between my 5 client pc's and 1 server.

I don't manage to reach this.
Using the merge replication upon all 4 tables works, but replicating only the owners table seems to be impossible.

Is it really impossible to replicate only the owners table, or do I forget something?

these are my create table scrips:
create table Owners (
id int identity,
name varchar(20),
firstname varchar(20),
address varchar(20),
phone varchar(20),
CONSTRAINT PK_owners
PRIMARY KEY(id)
);

create table cars (
id int identity,
licenceplate varchar(20),
ownerid int,
CONSTRAINT PK_cars
PRIMARY KEY(id),
CONSTRAINT FK_cars_ownerid
FOREIGN KEY(ownerid) REFERENCES owners(id) ON DELETE CASCADE
);

create table busses (
id int identity,
licenceplate varchar(20),
ownerid int,
CONSTRAINT PK_busses
PRIMARY KEY(id),
CONSTRAINT FK_busses_ownerid
FOREIGN KEY(ownerid) REFERENCES owners(id) ON DELETE CASCADE
);

create table ships (
id int identity,
licenceplate varchar(20),
ownerid int,
CONSTRAINT PK_ships
PRIMARY KEY(id),
CONSTRAINT FK_ships_ownerid
FOREIGN KEY(ownerid) REFERENCES owners(id) ON DELETE CASCADE
);

View 2 Replies View Related

Handling Merge Replication Foreign Key Conflicts

Sep 20, 2007

I have SQL CE clients replicating against a SQL Server 2005 db using merge replication. The DB has a table A and a table B, which has a foreign key to table A. It is common in my application for records in table A to be deleted on the server. I'm running into issues when a table A record has been deleted, but table B records were created on the clients which point to that record. When I sync I get a conflict because the table B records cannot be applied at the server, and the table A delete cannot be applied at the client.

What I would like to happen is to have the table B records on the client be deleted by the merge process, and to create a log of the event. I've looked into creating a business logic handler to do this, but I'm not sure what type of conflict this is (UpdateDeleteConflict or otherwise), and I'm not sure that deleting the table B records is something I can do in the business logic handler.

This seems like it would be a common problem in merge replication. I'm not locked into using a custom business logic handler at all. Any suggestions are welcome.

Thanks.

View 3 Replies View Related

Replication :: Difference Between Snapshot And Transaction And Merge Replication?

May 26, 2015

What is the main difference between snapshot and transactional and merge replication?

View 5 Replies View Related

How Do We Add A New Column To A Merge Replication Article, But Specify It As Not For Replication?

Aug 30, 2007

Hi all,

I know that adding a column using ALTER TABLE to add a column automatically allows SQLSERVER 2005 to replicate the schema changes to the subscribers, however, I would like to add a new column to an existing article that is being used for merge replication, however, I don't want this column to be replicated. Re-initialising the subscriptions is not a option. Help would be appreciated.

I am using SQLSERVER 2005 (SP1).

View 3 Replies View Related

Merge Replication Set Off Transactional Replication

Oct 9, 2007

I am working on bringing our disaster recovery site to be a live site. Currently we replicate to one of out servers (server B) with merge replication (from server A). Server A also does one way transactional replication form some table to several other servers including servers at the DR site.

This setup is not going to be fast enough for what we need so I am wondering if a table is receiving merge replication will the merge updates also replicate down the transaction path??

Example...
Server B update a row and merges to Server A. With this update them replicate (via transactional) to Server C??

thanks...

View 5 Replies View Related

Transactional Replication And Foreign Keys

Feb 7, 2007

When I use Management Studio (SQL Server 2005 SP 1) to set up transactional replication with updateable subscriptions, the snapshot initialization doesn't push the foreign key constraints. If I add a foreing key at the publisher, the change doesn't make it to the subscriber, even though the Publication Properties' Subscription Options panel shows Replicate Schema Changes set True - what am I missing?

Thanks, Liston

View 1 Replies View Related

Replication :: Copy Foreign Key Constraints Value Is Always False

Sep 23, 2015

I want to replicate the foreign keys to secondary.I changed the value Copy foreign key constraints value is to True.

I changed this value at pub properties - Articles -

And then it is asking for MARK for reinitialization with the new snapshot.I clicked ok.

When I checked sync status it has given the message like initial snapshot is not yet available.I started the snapshot and the subscription started replication records.When check at pup properties the value Copy foreign key constraints again false.

After changing the value to true it is showing as False.

View 3 Replies View Related

One Way Merge Replication

Feb 14, 2002

Hi everbody,
I setup the Merge Replication , it is working perfectly. But i have one problem now it is updating both ways. I nedd one way. Any body tell me which parameter i have to change.

Thanks in advance