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