FK Enforce for Replication setting

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Replication (2005)

FK Enforce for Replication setting

Author

Topic

Ken Blum
Constraint Violating Yak Guru

383 Posts

Posted - 2010-01-19 : 16:45:40

Is there a way to set the "Enforce for Replication" setting on all Foreign Keys within a published database without having to modify each one individually?

Or better yet, is there some global setting that can be used to automatically set this to No?

TIA,

Ken

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2010-01-19 : 17:15:17

You'd need to write a custom script for that. But why do you need the FKs in the subscriber database enforced? We don't even bother creating the FKs in the subscriber as the subscriber is treated as read-only, so only the publisher is able to write to it.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

"Let's begin with the premise that everything you've done up until this point is wrong."

Ken Blum
Constraint Violating Yak Guru

383 Posts

Posted - 2010-01-20 : 08:25:33

Tara:

The subscriber DB is the DB that contains all of the updates/inserts to the DB. This DB is setup by a third party vendor. I backup the DB, restore it to our server, and use that as the published DB and setup replication. We do this in order to use Web Synchronization, eliminating the need for our customers to setup any special firewall rules.

That's why I need to disable the FK constraints - we don't include all tables in the articles of publication, so some of the FK contstraints on the published DB tables cause problems during synchronization. I know I could setup a script to delete/update each FK, but I was looking for a better solution where I could possibly run a script that would update some "NotForReplication" column in a system table for each database. Do you know where that information is stored within the system tables?

TIA,

KTB

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2010-01-20 : 09:26:14

In SSMS, set the output to text. Execute the following script and copy/paste the results back into your query window.

To DISABLE the FKs of replicated tables:

select	'alter table [' + t.name + '] nocheck constraint [' + fk.name + '];
go'
from	sys.foreign_keys fk
join	sys.tables t
on	t.object_id = fk.parent_object_id
where	is_replicated = 1

To DROP the FKs (better)

select	'alter table [' + t.name + '] drop constraint [' + fk.name + '];
go'
from	sys.foreign_keys fk
join	sys.tables t
on	t.object_id = fk.parent_object_id
where	is_replicated = 1

Ken Blum
Constraint Violating Yak Guru

383 Posts

Posted - 2010-01-20 : 10:07:32

That's great Russell! Thanks a lot. Just one more question. What would the syntax be for the Alter Table statement to just change the constraint to NOT FOR REPLICATION? I can't seem to figure that one out.

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2010-01-20 : 10:21:29

I don't think it can be done in an ALTER TABLE statement.

Have a look at these:

http://technet.microsoft.com/en-us/library/ms147887.aspx

http://technet.microsoft.com/en-us/library/ms174386.aspx

You should be able to write a query to loop through all of 'em and change the bit, or to output multiple statements like above.

But I must agree with Tara -- I would drop all the FKs at the subscriber.

Ken Blum
Constraint Violating Yak Guru

383 Posts

Posted - 2010-01-20 : 11:29:42

Ah - too bad. So close. At least this gives me a way to identify which FK's need to be changed on the publisher. I don't really want to get into writing SQL scripts to create/alter the articles.

I can't drop the constraints on the subscriber since that is where all of the database updates/inserts occur. We synchronize off-site tables (subscriber) to our server (publisher) for reporting purposes. We use Web Synchronization to do this, in order to alleviate any special firewall rules at the off-site server. We do not synchronize all tables & that's where the problems occur when the constraints are enabled for replication.

I guess I will just have to change the Enable for Replication property on the publicshed DB in SSMS by hand. At least now I will know which FK's need to be changed so I don't have to look at every FK.

Thanks for your help!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources