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?
integrity is checked when the data are actually intered, update or modified. It is not a good prectice to turn on/off referential integrity check .
What you should do is to arrange the table order that you use to replicate the data. Therefore whenever a record has a referential integrity check, the parent key will recide in the parent table. For example if you hace a reference table and a transaction table, that has a reference to the reference table, always insert the reference table records first, before inserting the transaction table's record.