What's the best way to ensure referential integrity on a replicated database?

Question

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?

Thanks

+1 Decent question, not sure why it's downvoted – Andomar Feb 20 '12 at 18:27 — Andomar, Feb 20 '12 at 18:27
Cross posted: https://dba.stackexchange.com/q/13510/5203 – GSerg Dec 20 '17 at 18:03 — GSerg, Dec 20 '17 at 18:03

score 0 · Answer 1 · answered Feb 20 '12 at 18:18

0

Using replication will not guarantee you referential integrity

Referential integrity is mainted when a row is deleted from the primary table and the record still exists in its foreign key table where it is refrenced

Using primary key and foreign key will guarantee it and just for additional information there are comand like "ON DELETE CASCADE" will delete the key from primary if deleted from foreign key

answered Feb 20 '12 at 18:18

Ruzbeh Irani

2,318
18
10

I understand referential integrity on Source DB. How do you maintain / ensure that records are Inserted/Deleted into Replication DB applying rules of Referential Integrity? – amrit Feb 20 '12 at 19:14

score 0 · Accepted Answer · answered Feb 20 '12 at 21:29

0

Assuming replication is transactional and you have referential integrity on your publisher, then any subscribers will be in sync and therefore referential integrity will be maintained. The order of inserts is consistent.

answered Feb 20 '12 at 21:29

Barry King

683
1
6
21

What's the best way to ensure referential integrity on a replicated database?

2 Answers2

Linked