12.19.4 Data manipulation and integrity

When a referenced or referencing table is updated, added to, or deleted by a data manipulation SQL statement (excluding the PURGE TABLE statement), HiRDB performs checking during execution to guarantee data integrity. However, if the operations described in Tables 12-21 and 12-22 are executed, data integrity may no longer be guaranteed. If you specify USE in the pd_check_pending operand and perform these operations, the referencing table is placed in check pending status.

Table 12-21 Operations on referenced tables that nullify the guarantee of data integrity and the conditions under which loss of data integrity occurs

Operation on table or RDAREACondition for loss of data integrity
Database load utility (pdload)Data load of creation mode (-d option)The loaded primary key column does not contain a value that is the same as a value in a foreign key column of the referencing table.
Database reorganization utility (pdrorg)Reload (-k reld)The reloaded primary key column does not contain a value that is the same as a value in a foreign key column of the referencing table.
Reorganization (-k rorg)UOC was used to delete a row that contains a value that is the same as a value in a foreign key column of the referencing table.
Database structure modification utility (pdmod)Reinitialization of RDAREA (initialize rdarea)The referencing table is stored in an RDAREA that is different from the re-initialized RDAREA.
PURGE TABLE statementData exists in the referencing table.
Modification of table partition storage conditions by the ALTER TABLEAs a result of partitioning or integration of RDAREAs, a row that contains a value that is the same as a value in the foreign key column of the referencing table is not included.

Table 12-22 Operations on referencing tables that nullify the guarantee of data integrity and the conditions under which loss of data integrity occurs

Operation on table or RDAREACondition for loss of data integrity
Database load utility (pdload)Data loadThe loaded foreign key column does not contain a value that is the same as a value in the primary key column of the referenced table.
Database reorganization utility (pdrorg)Reload (-k reld)The reloaded foreign key column does not contain a value that is the same as a value in the primary key column of the referenced table.
Organization of this subsection
(1) When the target table is a partitioned table
(2) Other conditions under which loss of data integrity may occur

(1) When the target table is a partitioned table

If the target table is a partitioned table and the table contains mismatched data, execution of a utility may move the RDAREA in which the mismatched data is stored. For example, assume there is mismatched data in RDAREA 1 for a table that is partitioned and stored in RDAREAs 1, 2 and 3. Executing a utility could cause the mismatched data to be moved to RDAREA 3. The following table lists conditions that cause moving of mismatched data in a table between RDAREAs.

Table 12-23 Conditions that cause moving of mismatched data in a table between RDAREAs when the target table is a partitioned table

Operation on table or RDAREAConditions that cause moving of mismatched data in a table between RDAREAs
Database reorganization utility (pdrorg)Reorganization (-k rorg)You perform the following steps in the order listed below on a table partitioned using flexible hash partitioning or a matrix-partitioned table whose second dimension partitioning column is partitioned using flexible hash partitioning:
  1. Perform data load for each RDAREA.
  2. In HiRDB/Single Server, execute reorganization for each table.#
    In HiRDB/Parallel Server, specify the -g option to execute reorganization for each table.#
Rebalancing utility (pdrbal)You add an RDAREA for a table that has mismatched data to execute the rebalancing utility (pdrbal).#
#
You cannot execute the utility when you specify USE in the pd_check_pending operand if the target table is in check pending status.

(2) Other conditions under which loss of data integrity may occur

When all of the following conditions are met, data mismatch may occur; therefore, you need to check data integrity. For details about how to check data integrity, see 12.19.5 Procedure for checking table integrity. These conditions are the same regardless of whether the referential constraint action is RESTRICT or CASCADE.

When you manipulate referenced tables and referencing tables, make sure that all the above conditions are not true at the same time. You can guarantee data integrity by locking the target table with the LOCK statement's shared mode or lock mode. Note that there are some adverse effects on concurrent execution efficiency.