Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

Using SQL SERVER 2005, I have a couple of questions on Replication and referential integrity.

1) Does Replication handle referential integrity?

2) If I do an Insert to Parent table and then to Insert to Child table, in one transaction, on Source DB - will Replicated DB also behave in the same manner? I.e. In Replicated DB record must be present in Master table, before it is referenced in child table?

share|improve this question

migrated from serverfault.com Feb 20 '12 at 19:49

This question came from our site for system and network administrators.

3  
Replication just repeats the same events on the subscriber. So it'd be pretty hard to violate RI on the subscriber without also violating RI on the publisher. –  Aaron Bertrand Feb 20 '12 at 20:16

2 Answers 2

Replicated data (articles) maintain the same data and schema across the subscribers as that on the publisher. In the case of transactional replication, the data changes are applied within the same transactional boundaries as those happening at the publisher.

In short, the referential integrity will be maintained.

share|improve this answer

Aaron and Stanley are correct, for the most part referential integrity will be maintained. However, some consideration must be made if your foreign key constraints are marked as NOT FOR REPLICATION.

If foreign key constraints are marked NFR then all bets are off. While this usually isn't a big deal, and is usually needed, there can be a temporary violation of referential integrity. If foreign key constraints are marked NFR then it's best to periodically validate replicated data.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.