SQL Server Performance Forum – Threads Archive

replication vs foreign keys

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
);

---

Do you have the failed changes conflict?
http://msdn2.microsoft.com/en-us/library/ms151870.aspx
http://support.microsoft.com/kb/307356 Satya SKJ
Microsoft SQL Server MVP
Writer, Contributing Editor & Moderator
http://www.SQL-Server-Performance.Com
This posting is provided AS IS with no rights for the sake of knowledge sharing. The greatest discovery of my generation is that a human being can alter his life by altering his attitudes of mind.

---

I don’t think you can do this for merge replication because you trying to break your own rule…
FK are enforcing the data integrity… http://msdn2.microsoft.com/en-us/library/aa237486(SQL.80).aspx
Foreign Keys
When creating a merge publication, specify the tables that are included as articles in that publication. If you include tables that contain foreign keys, the referenced table should also be included in the publication. If an attempt is made to add new rows to an article referencing a primary key in a missing table, the insert fails because SQL Server 2000 cannot find the required primary key. If an attempt is made to update data in an existing row(s) of the article, the update succeeds because SQL Server 2000 does not have to add a new row(s) and key(s). After they are created, merge publications can be modified to include additional articles. You can add any missing, referenced tables to a publication if you discover that an article must be updated with additional rows and not just with modifications to existing rows. Use the publication properties dialog box to add the missing table.
MohammedU.
Moderator
SQL-Server-Performance.com All postings are provided “AS IS” with no warranties for accuracy.

---

That would be indeed the case for the Cars, Busses and Ships table (which have a reference to the owners table). But the owners table has no references ?!
So it MUST be possible to merge only the owners table (or not?)
or I understand something wrong? (by the way: if i do snapshot replication for only the owners table, that works)

---

I have more information.<br />I really hope you can help me further, because i’m already struggling several days on it.<br /><br />Like i told previously, when i do snapshot replication, i have never problems.<br />But only with the merge replication the problem occurs.<br /><br />However, I just managed to make it work by doing this via merge replic:<br />- in the subscriber database, the tables should not exist in advance. Then the snapshot replication (which is initially invoked when installing the merge replicaiton), initially creates the owners table with the correct data.<br /><br />- it also works when ONLY the owners table is created manually in advance (in the subscriber).<br /><br />- but when the "busses" AND the "owners" table are installed both in advance, then it seems to fail. the rowguid column is not added, and the data is not copied neither. <br /><br />(and it’s just the third case that i need in our production environment, because schema’s have to be installed in advance on all servers) [<img src=’/community/emoticons/emotion-6.gif’ alt=’‘ />!]

---

In snapshot replication, objects will be created and data will be copied if there are no objects exists
or if the objects exists they will be dropped and recreated but when you have the FK then you may get the error when it is trying to drop the object in that case you have change the snapshot option to "Delete all data in the existing table" if you check the check box "Include declared referential integrity"… In merge replication you can’t exclude table which has refferential integrity and make sure you have check the option "Include declared referential integrity" Note: Include all table which has refferential integrity in one publication…
MohammedU.
Moderator
SQL-Server-Performance.com All postings are provided “AS IS” with no warranties for accuracy.

---

]]>