Apply Referential Integrity

Administration | Maintenance | Apply Referential Integrity command

Summary

Referential Integrity can be either applied or removed from a PayGlobal database. Referential Integrity ensures all references between database tables are valid when records are created or updated.

Note: You must turn off referential integrity before you upgrade PayGlobal. Remember to turn it back on again after the upgrade is complete.

Setup

Important: PayGlobal will not apply or remove referential integrity successfully while a user is editing a record.
Ensure that you have exclusive access to the database before you run the Apply Referential Integrity command.

When you double-click Apply Referential Integrity, the Referential Integrity Setup form appears. This form indicates whether Referential Integrity is currently turned on or off.

You have the following options:

Add referential integrity to database:

Use this option to turn Referential Integrity on. You can also:

Check existing relationships.
Allow wildcards for job, cost centre and budget centre codes.

Remove referential integrity from the database:

Use this option to turn Referential Integrity off.

Verbose output (report successes):

When this checkbox is ticked, the log file shows successful and unsuccessful actions. When this checkbox is not ticked, the log file only shows unsuccessful actions.

Important: If you want to use the Manage Tables function, then you must ensure Referential Integrity is removed. If Referential Integrity is turned on, then it will not allow records that are referenced to be removed.

After running the command to either apply or remove Referential Integrity, you will be returned to the Referential Integrity Setup window. Click the Close button or press Esc to continue.

Advantages of turning Referential Integrity ON

Referential Integrity ensures that data is consistent across parent and child tables. It prevents the following scenarios:

Records from being added to a related table if there is no associated record in the primary table. For example, adding a current allowance with a job code that does not exist in the jobs table.
Values from being changed in a primary table that result in orphaned records in a related table. For example, changing a job code from 11001 to 11002.
Records from being deleted from a primary table if there are matching related records. For example, deleting a query when there are associated query clauses.

The result is that your data is more consistent.

PayGlobal also provides protection, for example, by limiting pick list selections.

At database level Referential Integrity provides extra protection against the following:

Development coding errors. If a developer makes a mistake that is not picked up by testing, then code that is not validated in the application could end up in the database if Referential Integrity is not turned on.
Inconsistent application rules. Business rules may be applied inconsistently over multiple applications. For example, Web-based applications and the PayGlobal application. At database level Referential Integrity ensures application rules are implemented consistently.
Data manipulation without application protection. Some clients insert or change data outside PayGlobal without the rule protection and validation that PayGlobal provides. When Referential Integrity is not turned on, clients could insert incorrect data because no rules exist to prevent data inconsistencies.

Disadvantages of turning Referential Integrity ON

Data entry is more restrictive. This will prevent data inconsistencies or prevent a field being used for more than one purpose.
More error messages.
Important: You must turn off referential integrity before you upgrade PayGlobal. Remember to turn it back on again after the upgrade is complete.
When you go from Referential Integrity = 'off' to Referential Integrity = 'on' some processes may be more time consuming, especially if there is a lot of historical data, but your data will be more accurate and consistent.
Note: If you select Apply Referential Integrity and Check existing relationships is selected, but bad data is breaking the constraint, then Referential Integrity remains turned off. In this situation, you must fix the data before you implement Referential Integrity.

Definition

Referential integrity is the concept of maintaining foreign keys. A 'foreign key' exists when the primary key of a parent entity exists in a child entity. A foreign key requires that values must be present in the parent entity before like values may be inserted in the child entity.