In this installment, we will begin a discussion of how temporal referential integrity (RI) may be enforced with today's relational database management systems (RDBMSs). In addition, we will expand the glossary which we began last time, in Part 19. Indeed, the glossary work has proven to be the major portion of the work done here in Part 20. This seems to be the right point in our series to begin formalizing the definitions of the terms we have introduced.
Object RI and Temporal RI: A Clarification
In Part 19, we said that by "object RI" we meant the normal DBMS-enforceable referential integrity, implemented by populating a column designated as a foreign key to table X with the value of one of the primary keys in that table. That was wrong, and is indicated as such in the glossary by shading that entry. A new corrected definition is also included.
On that basis, we went on to give the following definition of "temporal RI": If there is a temporal RI dependency from version table Y to version table X (not necessarily distinct), then no state of the database is valid in which any row in table Y is not object-RI linked to a row in table X, or in which the effectivity time period of a row in table Y is not wholly contained within the effectivity time period of its parent row in table X. No inserts, updates or deletes are valid that would transform a database into such a state.
As this discussion should make clear, our corrected definition of the term "object RI" does not require us to amend the above definition of "temporal RI."
Notice that in this definition, the dependent-on table (X) is itself a version table. Consequently, it contains no rows for objects, only rows for versions of objects. To illustrate, consider this schematic of a version table, also copied from Part 19. We also add a schematic of a dependent table which, in our ongoing example, is the Policy table.
Figure 1: Version Table Schema for Version Pattern 6
In the Policy table shown here, the notation (OFK) stands for object foreign key. The object mentioned in this definition is, in this example, a specific client. The client is represented by the OFK column client-nbr.
If the referenced table were itself an object table, and not a version table, then client-nbr would be a "normal" foreign key, not an OFK. It would contain a value that was unique in the designated primary key column of the referenced table, and its validity could be checked by the standard RI mechanism of the DBMS (see Glossary). But the definition specifically states that it is a definition that involves two version tables. Consequently, client-nbr in the referenced table, by itself, is not a complete primary key. So it is not something the DBMS can enforce.
So what does "object RI" mean when there is no unique object to refer to, when, whatever it is, it is not enforceable by a RDBMS? By the same token, what kind of foreign key is an OFK?
An OFK column contains the value of a unique object even though a table with that value as primary key does not exist. It is as if, instead of Figure 1, versioning were implemented as shown in Figure 2.
