3.7 Constraint Management for Valid Time Support

This section describes considerations related to valid time support that affect referential integrity constraints and unique constraints.

• Referential Integrity Constraints
  
• Unique Constraints
  

3.7.1 Referential Integrity Constraints

If a referential integrity constraint exists between two version-enabled tables that have valid time support, the valid time periods of rows are considered when the constraint is enforced. For example, assume that a DEPARTMENTS table has a MANAGER_ID column that is a foreign key referencing the EMPLOYEE_ID column in an EMPLOYEES table (that is, the department manager must be an existing employee). If both tables are version-enabled with valid time support, and if an insert or update operation would result in a new DEPARTMENTS.MANAGER_ID value, the operation will fail if the DEPARTMENTS.WM_VALID value is not within the range of the EMPLOYEES.WM_VALID value for the employee who is being made the department manager. (That is, the operation will fail if the new department manager is not a valid employee for the time period specified or defaulted for the insert or update operation.)

If either or both tables in a referential integrity constraint are not version-enabled with valid time support, valid time periods are ignored in enforcing the constraint.

3.7.2 Unique Constraints

If a unique constraint exists in a version-enabled table with valid time support, the valid time periods of rows are considered when the constraint is enforced. For example, assume that an EMPLOYEES table has an EMPLOYEE_ID column that has a unique constraint. If an insert or update operation would result in a new EMPLOYEE_ID value that is the same as an existing EMPLOYEE_ID value, the operation will fail if the WM_VALID values of the existing and inserted rows overlap. (That is, the operation will fail if the new employee and an existing employee have the same ID numbers and their rows are both valid at any given time. However, the operation will succeed if the valid time periods for the two employees do not overlap.)