Post reply

Setting "Enforce For Replication" option

  • September 6, 2007 at 11:50 pm

    #374142

    Hi all,
     
    There is a setting in the design on Foreign Keys called "Enforce For Replication".
     
    We need to set this on every foreign key before creating a publication. This is something we do regularly on new databases.
     
    Is there a way of setting this via Transact SQL rather than the SQL Mgmt Studio gui ?  If I could write up a nice long script that set this on all the keys, it would save a heap of time and button clicking.
     
    I haven't been able to find any info on how to do this from SQL.   The "Not for replication" option isn't what we want either, because that would stop the keys themselves being replicated - that's not what I'm after.. I need the keys on the subscribed database, I just don't want them enforced during replication.
     
    Cheers
    Dave

  • September 7, 2007 at 11:59 am

    #732242

    >>>>>The "Not for replication" option isn't what we want either, because that would stop the keys themselves being replicated ... <<<<

    That is *not* correct NOT FOR REPLICATION just means that on the subscriber replication does not have to be affected by the FK constraint. Therefore that is EXACTLY what you are asking for.

    What determines if the FK is replicated or not is the @schema_option parameter.

    Cheers,


    * Noel

  • September 7, 2007 at 5:56 pm

    #732301

    Hi Noel,

    THanks for that.  I've just been doing some testing and you're right.  That's really confusing, they could have worded those options better! 

    The only way I've been able to find to modify the constraint and add NOT FOR REPLICATION is to drop it and re-create it.  Is this correct?   Is there a stored proc or anything that can assist here or is that the only way of doing it?

    Thanks, appreciate your help - I'm a newbie when it comes to replication and SQL in general to be honest!

    Cheers

    Dave

  • September 11, 2007 at 8:37 am

    #732812

    Dave,

    I join your feeling. As far as I know there is no other way but dropping it and recreating it with NFR. I understand this is painfully done as an after thought. But you will get other projects and on those you will make sure to enforce that with your developers.

    Good Luck,


    * Noel

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

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