1.9 Constraint Support with Workspace Manager

This section describes Workspace Manager considerations relating to the use of database constraints.

Parent topic: Introduction to Workspace Manager

1.9.1 Referential Integrity Support

Version-enabled tables can have referential integrity constraints, including constraints with the CASCADE and RESTRICT options; however, the following considerations and restrictions apply:

  • If the parent table in a referential integrity relationship is version-enabled, the child table must be version-enabled also. (The child table is the one on which the constraint is defined.) For example, consider the following EMPLOYEE and DEPARTMENT table definitions, with a foreign key constraint added after the creation (that is, the dept_id value in each EMPLOYEE row must match an existing dept_id value in a DEPARTMENT row). 

    CREATE TABLE employee (
  employee_id NUMBER,
  last_name VARCHAR2(32),
  first_name VARCHAR2(32),
  dept_id NUMBER);
CREATE TABLE department (
  dept_id NUMBER,
  name VARCHAR2(32);
ALTER TABLE employee ADD CONSTRAINT emp_forkey_deptid
  FOREIGN KEY (dept_id) REFERENCES department (dept_id)
  ON DELETE CASCADE;

    In this example, DEPARTMENT is considered the parent and EMPLOYEE is considered the child in the referential integrity relationship; and if DEPARTMENT is version-enabled, EMPLOYEE must be version-enabled also. In this relationship definition, when a DEPARTMENT row is deleted, all its child rows in the EMPLOYEE table are deleted (cascading delete operation).

  • A child table in a referential integrity relationship is allowed to be version-enabled without the parent table being version-enabled. The following example (slightly edited for readability) creates a parent table named pt and a child table named ct, specifies a foreign key relationship for the child table between its j column and the parent table's id column, and version-enables the child table. 

    SQL> create table pt(id integer primary key, j integer);
Table created.

SQL> create table ct(id integer primary key, j integer);
Table created.

SQL> alter table ct add foreign key(j) references pt(id);
Table altered.

SQL> exec dbms_wm.enableversioning('ct');
PL/SQL procedure successfully completed.

    Note that if the child table is already version-enabled when you want to add the referential integrity constraint, you must alter the child table's special <table-name>_LTS table while using BeginDDL and CommitDDL. For example: 

    SQL> create table pt(id integer primary key, j integer);
Table created.

SQL> create table ct(id integer primary key, j integer);
Table created.

SQL> exec dbms_wm.enableversioning('ct');
PL/SQL procedure successfully completed.

SQL> exec dbms_wm.beginddl('ct');
PL/SQL procedure successfully completed.

SQL> alter table ct_lts add foreign key(j) references pt(id);
Table altered.

SQL> exec dbms_wm.commitddl('ct');
PL/SQL procedure successfully completed.

  • The foreign key in a child table must refer to the primary key in the parent table. (See the examples in the preceding item.)

  • Primary key values in the parent table cannot be updated. For example, if DEPARTMENT is the parent table and EMPLOYEE is the child table, you cannot change the department ID of a department.

  • Multilevel referential integrity constraints are permitted on version-enabled tables. For example, the table EMPLOYEE(emp_id, dept_id) could have the constraint that the department ID must exist in the table DEPARTMENT(dept_id, dept_name, loc_id); and the table DEPARTMENT(dept_id, dept_name, loc_id) could have the constraint that the location ID must exist in the table LOCATION(loc_id, loc_name). However, all tables that are involved in multilevel referential integrity constraints must be version-enabled and version-disabled together, unless all the referential integrity constraints involved have the Restrict rule. If all the constraints involved have the Restrict rule, you can version-enable the tables either all together or one at a time with child tables preceding their parent tables. The table names must be passed as a comma-delimited list to the EnableVersioning and DisableVersioning procedures.

Workspace Manager uses the static data dictionary views ALL_WM_RIC_INFO and USER_WM_RIC_INFO (described in Workspace Manager Static Data Dictionary Views ) to hold information pertinent to referential integrity support.

If you need to add, drop, enable, or disable a referential integrity constraint that involves two tables, it is more convenient if you perform the operation before version-enabling the tables. However, you can add, drop, enable, or disable a referential integrity constraint that involves a version-enabled table if you follow these steps:

  1. If the parent table has been version-enabled, begin a DDL session specifying the parent table.

  2. Begin a DDL session specifying the child table.

  3. Alter the <table-name>_LTS table for the child table to add the foreign key constraint. If a version-enabled table is the referenced table, specify <table-name>_LTS for the parent table. (See DDL Operations Related to Version-Enabled Tables for information about <table-name>_LTS tables and performing DDL operations on version-enabled tables.)

  4. Commit the DDL changes specifying the child table.

  5. If the parent table has been version-enabled, commit the DDL changes specifying the parent table.

Example 1-3 adds a foreign key constraint. Assume that the EMPLOYEE and DEPARTMENT tables are version-enabled and are defined as follows: 

EMPLOYEE(emp_id number  primary key, dept_id number)
DEPARTMENT(dept_id number  primary key, dept_name varchar2(30))

Example 1-3 Adding a Referential Integrity Constraint

-- Begin a DDL session on the parent table.
DBMS_WM.BeginDDL('DEPARTMENT'); 

-- Begin a DDL session on the child table.
DBMS_WM.BeginDDL('EMPLOYEE'); 

-- Add the constraint between EMPLOYEE_LTS and DAPATMENT_LTS.
ALTER TABLE employee_lts ADD CONSTRAINT employee_fk FOREIGN KEY (dept_id)
   REFERENCES department_lts(dept_id);

-- Commit DDL on the child table (transfers the constraint on employee_lts
-- to employee and drops employee_lts). 
EXECUTE DBMS_WM.CommitDDL('EMPLOYEE'); 

-- Commit DDL on the parent table (drops the department_lts table). 
EXECUTE DBMS_WM.CommitDDL('DEPARTMENT');

If you are in a DDL session (that is, if you have called the BeginDDL procedure), you cannot add, drop, enable, or disable a referential integrity constraint that involves two tables if one table is version-enabled and the other is not version-enabled. Both tables must be version-enabled.

Parent topic: Constraint Support with Workspace Manager

1.9.1.1 Locking with DML Operations on Tables with Referential Integrity Constraints

When data manipulation language (DML) operations are performed on version-enabled tables that have referential integrity constraints, Workspace Manager acquired shared locks so that the following conditions are enforced:

  • During an insert operation or an update operation affecting the foreign key column on the child table, delete operations cannot be performed on the parent table. For example, if DEPARTMENT is the parent table and EMPLOYEE is the child table, during the time that a new employee is being added or an existing employee is being assigned to a different department, no departments can be deleted.

  • During a delete operation on the parent table, insert operations or updates operation affecting the foreign key column cannot be performed on the child table. For example, during the time that a department is being deleted, new employees cannot be added and existing employees cannot be assigned to different departments.

Note:

For general information about locking performed by Workspace Manager, including explanations of shared and exclusive locks, see Lock Management with Workspace Manager.

Multiple sessions can simultaneously perform either of the following, but not both of the following, DML operations simultaneously:

  • Insert operations or update operations affecting the foreign key column on the child table

  • Delete operations on the parent table

Multiple sessions can simultaneously perform any of the following Workspace Manager operations simultaneously:

  • Use the MergeTable procedure to apply changes to a child table or parent table in different workspaces.

  • Use the MergeTable procedure to apply changes to a child table in one workspace, and insert or update the child table in another workspace.

  • Use the MergeTable procedure to apply changes to a parent table in one workspace, and delete from the parent table in another workspace.

One session will be blocked until the other session finishes in the following situations:

  • A session tries to merge changes to a child table in one workspace, and another session tries to merge changes to the parent table in another workspace.

  • A session tries to merge changes to a child table in one workspace, and another session tries to delete from the parent table.

  • A session tries to merge changes to a parent table in one workspace, and another session tries to insert into a child table or change a value in the foreign key of a child table.

Parent topic: Referential Integrity Support

1.9.2 Unique Constraints

Tables with unique constraints defined on them can be version-enabled. The following are supported:

  • UNIQUE constraint on a single column or multiple columns

  • Unique index on a single column or multiple columns

  • Functional unique index on the table

The treatment of null values is the same for version-enabled tables as for tables that are not version-enabled.

Workspace Manager uses the following static data dictionary views (described in Workspace Manager Static Data Dictionary Views ) to hold information pertinent to support for unique constraints:

Parent topic: Constraint Support with Workspace Manager

1.9.3 SET NULL Constraints

SET NULL constraints are not supported by Workspace Manager. If a table has any SET NULL constraints, they are converted to the RESTRICT option when the table is version-enabled.

For example, the constraint ON DELETE SET NULL is converted to ON DELETE RESTRICT.

Parent topic: Constraint Support with Workspace Manager