Using Cross-Database Referential Integrity Constraints

Usage considerations for cross-database referential integrity constraints.

  • When you create a cross-database constraint, the SAP ASE server stores the following information in the sysreferences system table of each database:

    Information Stored for Referential Integrity Constraints

    Information Stored in sysreferences

    Columns with Information About the Referenced Table

    Columns with Information About the Referencing Table

    Key column IDs

    refkey1 through refkey16

    fokey1 through fokey16

    Table ID

    reftabid

    tableid

    Database ID

    pmrydbid

    frgndbid

    Database name

    pmrydbname

    frgndbname

  • You can drop the referencing table or its database. The SAP ASE server automatically removes the foreign-key information from the referenced database.

  • Because the referencing table depends on information from the referenced table, the SAP ASE server does not allow you to:
    • Drop the referenced table,

    • Drop the external database that contains the referenced table, or

    • Use sp_renamedb to rename either database.

    You must use alter table to remove the cross-database constraint before you can do any of these actions.

  • Each time you add or remove a cross-database constraint, or drop a table that contains a cross-database constraint, dump both of the affected databases.

    Warning!   Loading earlier dumps of databases containing cross-database constraints may cause database corruption.
  • The sysreferences system table stores the name and the ID number of the external database. The SAP ASE server cannot guarantee referential integrity if you use load database to change the database name or to load it onto a different server.

    Warning!   Before dumping a database to load it with a different name or move it to another SAP ASE server, use alter table to drop all external referential integrity constraints.