Cross-Database Constraints and Loading Databases

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 of an earlier version 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.

  • Simultaneously dump all databases that reference each other. 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 may 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 the database dumps, cross-database constraints can become inconsistent.

  1. Put both databases in single-user mode.
  2. Drop the inconsistent referential constraint.
  3. 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)
  4. Fix any data inconsistency problems.
  5. Re-create the constraint.
Parent topic: Backing Up and Restoring User Databases