Sql-server – Foreign key constraints check during replication

foreign keymerge-replicationreferential-integrityreplicationsql server

I am a little bit new to replication and can't seem to find an answer to this anywhere.
Let's say I have a simple relation in my database: Department table with Code column as PK and Person table referencing Department by Person.DepartmentCode -> Department.Code.

ALTER TABLE [dbo].[Person]  WITH CHECK ADD  CONSTRAINT [FK_Person_Person]
FOREIGN KEY([DepartmentCode])
REFERENCES [dbo].[Department] ([Code])

Now, I would like to implement merge replication to a branch database of only relevant departments and people. I could set up filtering by DepartmentCode in all the tables that will be partitioned, which would do the trick. But I wanted to see if I could leverage referential integrity to do part of the filtering for me. I.e. only filter dbo.Department and rely on constraints to not let irrelevant people into the replica's dbo.Person. However, what I get in the replica is the dbo.Department with filtering properly applied and dbo.Person with DepartmentCode values breaking the foreign key constraint (and the new records with "wrong" DepartmentCode continue to be replicated into the branch's DB). The constraint is applied and enforced for any other transactions, though. So, if I try to insert a record breaking the integrity with a user INSERT, I can't.

So it looks like the referential constraint isn't being checked during replication of the data.
1. Is there a way to enforce constraint checking for data inserted by replication?
2. Would it be a good idea to use that for filtering/partitioning?

Best Answer

Foreign Key Constraints, Check Constraints, IDENTITY columns and Triggers all support the NOT FOR REPLICAION option to control whether they are active when the replication agent updates subscribers.

See Disable Foreign Key Constraints for Replication.