Inter-Database Foreign Key References

When a system-generated key value is assigned to a record, the system also stores the key value in a key table that corresponds to the record's database table (see Defining Table Options). Key tables store the universal environment identifier along with the key values.

When data is moved from production to archive, the archived records' related key table records are not deleted from the production database. The key table records are instead updated with the universal environment identifier of the target archive environment. This prevents the system from re-using an archived key value. For example, since field activity steps are allowed to reference archived meter reads, the key values of the archived meter reads cannot be assigned to new meter reads.

Note: Imagine. What would happen if archived key values were re-used? New records added to production might be unwittingly linked to records that were simply preserving their link to archived records.

If the production system encounters a foreign key reference to a record that is not in the current environment's database, it will look up the key value on the record's associated key table to see if the underlying data has been archived. The existence of the key value on the key table satisfies application level referential integrity, because the key of the archived data is still present in the production database. Even though the key is displayed, the description of the object is blank, and any go-to functionality that is normally associated with the key is disabled.