If you use the references constraint of create table or alter database to reference tables across databases, you may encounter problems when you try to load a dump of one of these databases.
If tables in a database reference a dumped database, referential integrity errors result if you load the database with a different name or on a different server from where it was dumped. To change the name or location of a database when you reload it, use alter database in the referencing database to drop all external referential integrity restraints before you dump the database.
Loading a dump of a referenced database that is earlier than the referencing database may cause consistency issues or data corruption. As a precaution, each time you add or remove a cross-database constraint or drop a table that contains a cross-database constraint, dump both affected databases.
Dump all databases that reference each other at the same time. To guard against synchronization problems, put both databases in single-user mode for the dumps. When loading the databases, bring both databases online at the same time.
Cross-database constraints can become inconsistent if you:
Do not load database dumps in chronological order (for example, you load a dump created on August 12, 1997 after one created on August 13), or
Load a dump into a database with a new name.
If you do not load, cross-database constraints can become inconsistent.
To remedy this problem:
Put both databases in single-user mode.
Drop the inconsistent referential constraint.
Check the data consistency with a query such as:
select foreign_key_col from table1 where foreign_key not in (select primary_key_col from otherdb..othertable)
Fix any data inconsistency problems.
Re-create the constraint.
