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 13-20 and 13-21 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 13-20 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 RDAREA | Condition 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. |
Reflection processing of updatable online re-organization (pdorend command) | You perform any of operations (1) to (4) below on the replica RDAREA and original RDAREA in the current database during operation of updatable online reorganization# on tables having a referential relationship (data mismatch occurs after reflection processing):
| |
PURGE TABLE statement | Data exists in the referencing table. | |
Modification of table partition storage conditions by the ALTER TABLE | As 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 13-21 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 RDAREA | Condition for loss of data integrity | |
---|---|---|
Database load utility (pdload) | Data load | The 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. |
Reflection processing of updatable online re-organization (pdorend command) | You perform any of operations (1) to (5) on the replica RDAREA and original RDAREA in the current database during operation of updatable online reorganization# on tables having a referential relationship (data mismatch occurs after reflection processing):
|
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. Table 13-22 lists conditions that cause moving of mismatched data between RDAREAs.
Table 13-22 Conditions that cause moving of mismatched data in a table between RDAREAs when the target table is a partitioned table
Operation on table or RDAREA | Conditions 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:
|
Rebalancing utility (pdrbal) | You add an RDAREA for a table that has mismatched data to execute the rebalancing utility (pdrbal).# |
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 13.18.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.