SQL Server Performance

replication vs foreign keys

Discussion in 'SQL Server 2005 Replication' started by stt, Apr 10, 2007.

  1. stt New Member

    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
    );
  2. satya Moderator

  3. MohammedU New Member

    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.
  4. stt New Member

    quote:Originally posted by MohammedU
    If you include tables that contain foreign keys, the referenced table should also be included in the publication.

    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)
  5. stt New Member

    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=':(' />!]
  6. MohammedU New Member

    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.

Share This Page