Access path support for referential integrity in SQL2

Abstract.

The relational model of data incorporates fundamental assertions for entity integrity and referential integrity. Recently, these so-called relational invariants were more precisely specified by the new SQL2 standard. Accordingly, they have to be guaranteed by a relational DBMS to its users and, therefore, all issues of semantics and implementation became very important. The specification of referential integrity embodies quite a number of complications including the MATCH clause and a collection of referential actions. In particular, $\hbox{{\tt MATCH PARTIAL}}$ turns out to be hard to understand and, if applied, difficult and expensive to maintain. In this paper, we identify the functional requirements for preserving referential integrity. At a level free of implementational considerations, the number and kinds of searches necessary for referential integrity maintenance are derived. Based on these findings, our investigation is focused on the question of how the functional requirements can be supported by implementation concepts in an efficient way. We determine the search cost for referential integrity maintenance (in terms of page references) for various possible access path structures. Our main result is that a combined access path structure is the most appropriatefor checking the regular MATCH option, whereas $\hbox{{\tt MATCH PARTIAL}}$ requires very expensive and complicated check procedures. If it cannot be avoided at all, the best support is achieved by a combination of multiple $\mbox{B}^*$-trees.