Setting "Enforce For Replication" option

Question

Setting "Enforce For Replication" option

floyd99

Mr or Mrs. 500

Points: 571
More actions
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

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

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

noeld SSC Guru Points: 96590 More actions · Answer 1

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

floyd99 Mr or Mrs. 500 Points: 571 More actions · Answer 2

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

noeld SSC Guru Points: 96590 More actions · Answer 3

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