Post reply

FK constraint error during replication envolving tables without replication

  • July 4, 2008 at 10:14 am

    #189206

    Hi,

    Here is the scenario:

    There is a system called PeoplePlay.

    This system is based on two databases, one is in the main CPD running Sql Server 2005 and the other side are clients on the streets using handhelds with windows mobile and SQL Server Compact;

    SQL Server 2005 (will be called "SS") in one side (at the central office) and SQL Server Compact (will be called SC) to the other side (inside the handhelds);

    The handheld users go out on the streets to gather new system P PeoplePlay user's information;

    SS has many "ccorporate tables such as country names; area codes; post codes etc.

    SC uses those "corporate" tables on its tables as FK such as UserProfileInfoDetails; ContractorInfoDetail etc. these tables has FK to my "corporate" tables to assure the integrity to the iimputedPostCode, to the iimputedAreaCode, CountryName etc. So I have FK from my eexclusivelyhandheld tables referencing my "corporate" tables (replicas at the handhelds)...

    Then here comes my problem:

    I need to keep all these "corporate" tables up-to-dated on my handheld clients, so I run the replication every day to aassurefresh data on them;

    It works fine if my replication does not send an entire new snapshot to my handheld. But if it does send a new snapshot (e.g. due to a new column added to the replication), the replication tries to drop all my "corporate" tables and then to recreate them. But the existing FK on my handheld tables does not allow the drop the corporate tables and them my replication stops. So, here are my questions:

    1) Is there somehow to make sure it can work without having to drop/disable all my FK before the replication?

    2) If the only solution to make it work is to disable/drop my FK b4 the replication, how can I do that using my SC (SQL Server Compact!!!) , witch is the one who aactuallystarts all the replication process and I can't find any how to include scripts to the replication?

    Thank you for any help!

    Regards,

    Bruno.

  • July 4, 2008 at 10:46 am

    #838403

    Just use the "NOT FOR REPLICATION" clause when you create the constraints.

  • July 4, 2008 at 11:29 am

    #838414

    Thank you for your reply!

    I think I've tried that before and it didn't work properly. Please just let me be clear on this again:

    So, you mean I have to recreate the FK on my tables (those ones that are not in the replication context)? e.g.: my table UserProfileInfoDetails has a country field refencing by FK my Country table (witch is one of my replication tables). My UserProfileInfoDetails table is not into the replication context, but even so, I have to recreate it's FK using the NOT_FOR_REPLICATION=true clause?

    If so, I'll try it. I'll let you know how that goes on.

    Thanks!!

  • July 4, 2008 at 11:53 am

    #838417

    That's it, something like

    ALTER TABLE UserProfileInfoDetails DROP CONSTRAINT fk_Country

    Then

    ALTER TABLE UserProfileInfoDetails ADD CONSTRAINT fk_Country

    FOREIGN KEY (id) REFERENCES Country (id)

    NOT FOR REPLICATION

    Even though UserProfileInfoDetails is not part of replication, you may need to do this if you are making changes to the Country table via replication

  • July 7, 2008 at 11:02 am

    #839047

    Hi,

    I've tried to use you recommendation, but, later I found that unfortunately this solution won't work with my tables under Sql Server Compact due to limitations using NOT FOR REPLICATION. As you can read at http://msdn.microsoft.com/en-us/library/ms171864.aspx this SQL Server version does not accept this keyword. So, is there any other solution to recommend?

    Kindest regards,

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply