Results 1 to 6 of 6
  1. #1
    Join Date
    May 2003
    Posts
    3

    Unanswered: Replication and FK Constraint

    Let me preface this w/ the fact that I may be stupid...

    In trying to test replication in SQL 7 I created a staging database to hold one table (tblStaff) that I would try to replicate into the "live" version of tblStaff in another DB (on the same server).

    The "live" version of tblStaff obviously (perhaps) has a number of dependent tables (and stored procedures - which are really only select queries) that rely on the StaffID key. I have turned off the "Enable key for replication" in all the dependent tables and, for good measure, in tblStaff. I then created a publication of the stagingDB tblStaff and a pull subscription on the live DB. But...(snapshot) replication *still* fails w/ an error that dropping the live tblStaff would violate foreign key constraints.

    Thanks in advance.

  2. #2
    Join Date
    Aug 2002
    Location
    Scotland
    Posts
    1,578
    What was the error?
    --Satya SKJ
    Microsoft SQL Server MVP
    [IMG]http://sqlserver-qa.net/google_bart.gif[/IMG]

  3. #3
    Join Date
    May 2003
    Posts
    3
    "Could not drop/truncate (depending on the option chosen earlier in the process - tried it both ways) table due to foreign key constraint."

    As an (inelegant) workaround I wrote two scripts to drop and add the constraints and then synch. in the middle. It works and the table only has to be replicated every now and again but... I would still like to know the "real" way to do it.

  4. #4
    Join Date
    Aug 2002
    Location
    Scotland
    Posts
    1,578
    Use the NOT FOR REPLICATION Option and refer to BOL for more information.
    --Satya SKJ
    Microsoft SQL Server MVP
    [IMG]http://sqlserver-qa.net/google_bart.gif[/IMG]

  5. #5
    Join Date
    May 2003
    Posts
    3
    I already did that.

  6. #6
    Join Date
    Aug 2002
    Location
    Scotland
    Posts
    1,578
    May check this Article about do's and dont's.
    --Satya SKJ
    Microsoft SQL Server MVP
    [IMG]http://sqlserver-qa.net/google_bart.gif[/IMG]

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •