Ygthkb

Tikz: Perpendicular FROM a line

Is the tritone (A4 / d5) still banned in Roman Catholic music?

Are all power cords made equal?

Plotting a bump function

Can you help me solve this algebra problem?

Including proofs of known theorems in master's thesis

What is an explicit bijection in combinatorics?

Taking an academic pseudonym?

Is having explosions as a go-to solution considered bad table manners?

filecontents: select rows of group to display

What does "south of due west" mean?

Can't figure out a htaccess rule

Is it possible to methodically find the total of ways to read a given phrase making a stack?

Why is Shelob considered evil?

Third wheel character

Expression for "unconsciously using words (or accents) used by a person you often talk with or listen to"?

Alternate timeline nomenclature

What is the meaning of "usr"?

Color of alien seas

How do I fight with Heavy Armor as a Wizard with Tenser's Transformation?

If we can’t finish all tasks, does this mean we are doing Scrum wrong?

Boss asked me to sign a resignation paper without a date on it along with my new contract

How can guns be countered by melee combat without raw-ability or exceptional explanations?

Why write a book when there's a movie in my head?

Foreign key constraints check during replication?

PostgreSQL - insert/update violates foreign key constraintsReplication fails because of a non-existent foreign keyHow can I change Referential Integrity from a Unique Key Constraint to a Primary Key?Why are foreign keys ignored on subscribers?Deferrable Foreign Key ConstraintsForeign key constraints failure when cross updating between two databasesInsertion of data violates the foreign key constraintCreate foreign key constraint on indirect dataCreating index on foreign key column?Foreign Key Error with Transactional Replication from Backup

0

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?

sql-server replication foreign-key merge-replication referential-integrity

share

asked 5 mins ago

Yuri MakassioukYuri Makassiouk

636

add a comment | 

0

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?

sql-server replication foreign-key merge-replication referential-integrity

share

asked 5 mins ago

Yuri MakassioukYuri Makassiouk

636

add a comment | 

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?

sql-server replication foreign-key merge-replication referential-integrity

share

asked 5 mins ago

Yuri MakassioukYuri Makassiouk

636

ALTER TABLE [dbo].[Person]  WITH CHECK ADD  CONSTRAINT [FK_Person_Person]

FOREIGN KEY([DepartmentCode])

REFERENCES [dbo].[Department] ([Code])

share

asked 5 mins ago

Yuri MakassioukYuri Makassiouk

636

share

asked 5 mins ago

Yuri MakassioukYuri Makassiouk

636

asked 5 mins ago

Yuri MakassioukYuri Makassiouk

636

asked 5 mins ago

Yuri MakassioukYuri Makassiouk

636