
Integrity Constraints Generally
An integrity constraint is a rule that limits what values may occur in a column of a table or in columns of related tables. The Oracle 8i database system supports integrity constraints including the following:
In a VRDBS, enforcement of constraints generally must take into account the existence of the different versions V of the table T and of the relationships between the workspaces W that provide access to the versions and in which the versions are created.
In the second case, shown at 315, a parent workspace, here LIVE workspace 203(1), and a child workspace, here workspace 203(2), are involved. First, a user of workspace 203(2) inserts a row 319 into version 317. Since there is currently no record with “Smith” in its NAME field in version 317 in workspace 203(2), there is no unique key constraint violation. Then a user of LIVE workspace 203(1) inserts a row 321 into version 303; again, there is currently no record with “Smith” in its NAME field in version 303, so there is no constraint violation. However, when workspace 203(2) is refreshed from workspace 203(1) or workspace 203(2) is merged with workspace 203(1), the result is a unique key constraint violation, since the version resulting from the refresh or merger will have two records with NAME=“Smith”.
Constraints, Version Hierarchies, and Workspace Hierarchies
In order to optimize constraint checking in a VRDBS, it is necessary to determine the rows in the version-enabled table in which a constraint violation may occur as a result of a change in a particular version, since only those rows need be checked for constraint violations. The determination is made using the workspace hierarchy for the workspace in which the version is being changed and the version hierarchy for the version in which the change is being made. The workspace hierarchy determines what versions will actually be changed; the version hierarchy determines the rows in which a constraint violation may occur.
Visibility
The notions of version visible and version view are used to determine rows in which a constraint violation may occur. Version visible is defined as follows: a row R in a version-enabled table that has a primary key K and has the version number for a version V in its ver no field 503 is said to be version visible from a version V′ if the following holds:
The latest version of the key K is determined for a given version CV using the query
What the query does is find a version of row R with key K that has a version tag V where V is in the ancestry of the current version CV and was NOT versioned further in another version V′ where V′ is in the ancestry of the current version CV and V′ is a successor version of V. If a row is versioned further, it means that the further versioned row overrides the earlier versioned row. Whether a row gets overridden by a row in a future version is determined solely by the primary key, i.e., rows are chained between versions for given primary keys. The key involved in a particular constraint plays no part in determining what version of a row is to be seen from the current version.
The following provides an example of how rows that are version visible from a given row are determined. The versions 207 in
Version V′ is version 205(4). As shown in hierarchy 207, its ancestors are v 205(4), v 205(2), and v 205(1). As is indicated by the fact that version 205(2) of the row has no children, that version is still present in v 205(4) and the primary key for that row, namely the values of (emp_id 404, ver no 503), or (1,205(2)), is the latest version of the primary key. Consequently, the row with ver no 503=205(2) is the row that is visible to version V′ 205(4), while the row with ver no 503=205(1) is not visible to V′ even though it is in the ancestry of V′. The reason it is not visible is that the key (1,205(1)) is not the latest version of the primary key.
Using the concept of version-visible rows, we can now define a version view:
Again taking emp_table_LT 501 as our example, the version view of emp_table_LT 501 from version 403(1) is the rows of version 403(1) and the corresponding rows of its sole ancestor, version 403(0), since the rows in version 403(1) have primary keys which are the same as those in version 403(0) and are therefore the latest versions of the primary key relative to version 403(1).
The version view can then be used to determine which rows must be checked to determine whether a constraint has been violated. The rows that need to be checked will depend on the version hierarchy, the workspace hierarchy, and the operation that made the constraint check necessary. The constraint version view for a given operation is the set of versioned rows that need to be examined to enforce the constraint for the given operation. In general, the constraint version view for a given operation can be determined as follows:
The problem in checking constraint violations in a VRDBS is having the versions in the constraint version view readily available for checking. To do this, the preferred embodiment defines a few views that compute the list of the constraint version view. The views use the base tables shown at 701 in
The values in the fields of the tables of
Beginning with wm$ancestor_workspaces_table 703, table 703 is part of the metadata for the version-enabled tables described in the Description of related art. Each workspace in graph 735 but LIVE workspace 737 has at least one ancestor, and there is an entry for each ancestor in table 703. There are three columns in the table: workspaces 705, ancestor workspaces 707, and ancestor versions 709. Each workspace that has an ancestor has a row in table 703, with the workspace being specified in column 705. In the workspace's row, the value of the field in column 707 specifies an ancestor workspace of the workspace specified in col. 705 and the value of the field in column 709 specifies the current version of the table in the ancestor workspace.
Relationships between table versions in the workspaces are recorded in wm$version_hierarchy_table 733. Each row has three fields, field 735 identifying a version, field 737 identifying a parent version for the version in field 735, and field 739 identifying the workspace to which the version specified in field 735 belongs. The parent version is the version from which version 735 was originally copied. Thus, in table 733, v0 is in the LIVE workspace and has no parent, so field 737 has a null value and field 739 specifies the LIVE workspace, while v1, which is also in the LIVE workspace but has as its parent v0, has v0 in field 737 and LIVE in field 739.
The foregoing tables are further used in the creation of the constraint version view: wm$current_parent_versions_view, which is produced by a join of wm_ancestor_workspaces_table 703 and wm$version_hierarchy_table 733:
This view is used to present the latest versions of each primary key relative to CURRENT_WORKSPACE.
Optimistic Enforcement of Unique Key Constraints in a VRDBS:
Like other relational database systems, the Oracle 8i system defines a unique key constraint for a table as follows: A unique key integrity constraint on a set of columns (key) in a table T requires that every value in the column or set of columns (key) or a function applied to the key be unique; that is, no two rows of the table T have duplicate values in the specified key column(s) or in the return values of the specified function (for a functional constraint) on the key columns.
The following definitions are useful in understanding unique key constraints in VRDBS's:
Using the above terminology, an optimistic unique key constraint can be defined as follows:
The rows that need to be actually examined to determine whether the key columns are Workspace Unique are determined by the constraint version views for the particular operations and the unique key constraint. These constraint version views are set forth in the following:
The constraint check is performed when the explicit refresh or merge operation is performed.
The change propagation operations and their constraint version views for the various workspaces are the following, beginning with workspace hierarchy 201, in which there are no CR workspaces.
Continuing with workspace hierarchy 1301, it should be remembered that when a given workspace as a CR descendant, creation of a new workspace from the given workspace does not result in the creation of a new current version in the given workspace.
The chief views used in detecting violations of the unique key constraint are:
In addition, the VRDBS creates a view that is specific to a particular version-enabled table i.e. this view is created when a table is version-enabled and is used for the enforcement of unique key constraints for that particular table in conjunction with the generic metadata views described above. For example, when the table EMP is version-enabled, a view EMP_CONS is created. This view computes the most recent versions of rows that are tagged with versions in the All_Constraint_Versions_View list.
All the previously described views are used by the preferred embodiment to enforce unique key constraints in conjunction with various operations performed by the VRDBS on the version-enabled table and its versions.
A non-versioned table having a Unique Key Constraint defined on some column(s) has either a unique index or a non-unique index on these columns.