Unanswered: Referential integrity during replication
I like to build a custom replication application for a databasee.
To questions concering this. The idea is to use a mechanim, which updates table by table (in sequence).
During the replication proccess I update a table (table1), which has 1 to 1 refenceto a second table(table2). This new record is unsing a reference which hasn't been entered by this time into table 2. This is hurts the referencial integrity, right?
Would it help to use a transaction for the update in table 1 and table 2, when is the integrity checked? At the end of the transaction, by the command commit transaction?
Is it possible to switch on/off the referential integrity check while a database is running? If yes, are there any side effects beside that the integrity is not propre controlles anymore?
drop table test1
drop table test2
create table test2(id int primary key)
create table test1(id int, rid int)
ALTER TABLE test1 WITH NOCHECK ADD CONSTRAINT
FK1 FOREIGN KEY(rid) REFERENCES dbo.test2(id)
insert test2 values(1)
insert test1 values(1,1)
ALTER TABLE dbo.test1 NOCHECK CONSTRAINT FK1
insert test1 values(1,11) -- works