In this article, and in Part 10 and Part 11, we have been discussing the issue of foreign keys and referential integrity in versioned tables. The issue arises because most modelers and database administrators (DBAs) seem to believe that if a change is made in a versioned table, and there are foreign keys pointing to that table, those foreign keys must also be changed. If they themselves are in a versioned table, and foreign keys in yet other versioned tables point to the rows they are in, versioned updates will ripple out to those other tables, creating a cascade update problem.
In Part 10, we decided that our options are the options we always have for any problem. We can ignore the problem, avoid the problem or fix the problem. We also determined that we cannot ignore the problem because we are looking for an enterprise solution, one applicable to all databases. For databases containing small and/or infrequently updated versioned tables, we might be able to ignore the problem. But an enterprise solution must apply to all databases, regardless of size or frequency of update.
In Part 11, we suggested that the most perspicuous line of investigation was to begin by asking "What do foreign keys to versioned tables relate?" One answer is that they relate rows to rows. The "related-to" rows are the rows in versioned tables. The "related-from" rows may be either versions or non-versions. On the assumption that foreign keys relate rows to rows, whether or not any of those rows are in versioned tables, we developed one solution to the versioned foreign key problem. Here in Part 12, we develop a second solution to the versioned foreign key problem, also based on the assumption that those foreign keys still relate rows to rows. We will then develop a solution based on the assumption that foreign keys that point into versioned tables do not relate rows to rows, i.e., versions to versions, but rather relate objects to objects.
We will conclude our discussion of referential integrity involving versioned tables by distinguishing between the machinery of versioning and its semantics. The machinery is complex, and should be as invisible as possible to developers and users. But the semantics are the semantics of temporal databases, and once such databases are supported, those who write queries must understand those additional semantics in order to write unambiguous queries against those databases.
Chart of Version Patterns
Before continuing, we insert here the current chart of version patterns. References back to earlier patterns are happening pretty frequently now, and we hope this will help the reader recall what those prior version patterns are. They also give a hint of version patterns not yet discussed.
Chart of This Series
Before continuing, we insert here a chart listing the installments to date in this series, and a brief description of what each one is about. As this series grows longer, the back references to earlier installments will only increase, and we think that having a chart like this at hand will be helpful.
Solution 2. Don't Update Foreign Keys to Versions
