Referential constraints The table containing the parent key is called the parent table of the referential constraint, and the table containing the foreign key is said to be a dependent of that table Referential constraints are optional and can be defined in the CREATE TABLE statement or the aLter table statement Referential constraints are enforced by the database manager during the execution of INSERT, UPDATE, dELetE, ALTER TABLE ADD CONSTRAINT, and SET INTEGRITY statements Referential constraints with a delete or an update rule of RESTRICT are enforced before all other referential constraints. Referential constraints with a delete or an update rule of NO AcTiOn behave like RESTRICT in most cases Note that referential constraints, check constraints, and triggers can be Referential integrity rules involve the following concepts and terminology Parent key a primary key or a unique key of a referential constraint. Parent ro A row that has at least one dependent row. Parent table a table that contains the parent key of a referential constraint. A table can be a parent in an arbitrary number of referential constraints. A table that is the parent in a referential constraint can also be the dependent in a referential constraint. Dependent table a table that contains at least one referential constraint in its definition a table can be a dependent in an arbitrary number of referential constraints. a table that is the dependent in a referential constraint can also be the parent in a referential constraint Descendent table a table is a descendent of table T if it is a dependent of T or a descendent of a dependent of T. Dependent row A row that has at least one parent row Descendent row A row is a descendent of row r if it is a dependent of r or a descendent of a dependent of r. Referential cycle a set of referential constraints such that each table in the set is a descendent of itself 10 SQL Reference, Volume 1
The table containing the parent key is called the parent table of the referential constraint, and the table containing the foreign key is said to be a dependent of that table. Referential constraints are optional and can be defined in the CREATE TABLE statement or the ALTER TABLE statement. Referential constraints are enforced by the database manager during the execution of INSERT, UPDATE, DELETE, ALTER TABLE, ADD CONSTRAINT, and SET INTEGRITY statements. Referential constraints with a delete or an update rule of RESTRICT are enforced before all other referential constraints. Referential constraints with a delete or an update rule of NO ACTION behave like RESTRICT in most cases. Note that referential constraints, check constraints, and triggers can be combined. Referential integrity rules involve the following concepts and terminology: Parent key A primary key or a unique key of a referential constraint. Parent row A row that has at least one dependent row. Parent table A table that contains the parent key of a referential constraint. A table can be a parent in an arbitrary number of referential constraints. A table that is the parent in a referential constraint can also be the dependent in a referential constraint. Dependent table A table that contains at least one referential constraint in its definition. A table can be a dependent in an arbitrary number of referential constraints. A table that is the dependent in a referential constraint can also be the parent in a referential constraint. Descendent table A table is a descendent of table T if it is a dependent of T or a descendent of a dependent of T. Dependent row A row that has at least one parent row. Descendent row A row is a descendent of row r if it is a dependent of r or a descendent of a dependent of r. Referential cycle A set of referential constraints such that each table in the set is a descendent of itself. Referential constraints 10 SQL Reference, Volume 1
Referential constraints Self-referencing table a table that is a parent and a dependent in the same referential constraint. The constraint is called a self-referencing constraint Self-referencing row A row that is a parent of itself Insert rule The insert rule of a referential constraint is that a non-null insert value of the foreign key must match some value of the parent key of the parent table. The value of a composite foreign key is null if any component of the value is null This rule is implicit when a foreign key is specified Update rule The update rule of a referential constraint is specified when the referential constraint is defined. The choices are No action and RESTRICT. The update rule applies when a row of the parent or a row of the dependent table is updated In the case of a parent row, when a value in a column of the parent key is updated, the following rules apply If any row in the dependent table matches the original value of the key, the update is rejected when the update rule is RESTRICT If any row in the dependent table does not have a corresponding parent key when the update statement is completed(excluding AFTER triggers), the update is rejected when the update rule is NO ACTiON In the case of a dependent row, the NO ACTiOn update rule is implicit when a foreign key is specified. NO ACTION means that a non-null update value of a foreign key must match some value of the parent key of the parent table when the update statement is completed. The value of a composite foreign key is null if any component of the value is null Delete rule The delete rule of a referential constraint is specified when the referential constraint is defined. The choices are No action, restrict cascade, or SET NULL SET NULL can be specified only if some column of the foreign key allows null values The delete rule of a referential constraint applies when a row of the parent table is deleted. More precisely, the rule applies when a row of the parent table is the object of a delete or propagated delete operation(defined below), nd that row has dependents in the dependent table of the referential constraint. Consider an example where p is the parent table, d is the Chapter 1. Concepts 11
Self-referencing table A table that is a parent and a dependent in the same referential constraint. The constraint is called a self-referencing constraint. Self-referencing row A row that is a parent of itself. Insert rule The insert rule of a referential constraint is that a non-null insert value of the foreign key must match some value of the parent key of the parent table. The value of a composite foreign key is null if any component of the value is null. This rule is implicit when a foreign key is specified. Update rule The update rule of a referential constraint is specified when the referential constraint is defined. The choices are NO ACTION and RESTRICT. The update rule applies when a row of the parent or a row of the dependent table is updated. In the case of a parent row, when a value in a column of the parent key is updated, the following rules apply: v If any row in the dependent table matches the original value of the key, the update is rejected when the update rule is RESTRICT. v If any row in the dependent table does not have a corresponding parent key when the update statement is completed (excluding AFTER triggers), the update is rejected when the update rule is NO ACTION. In the case of a dependent row, the NO ACTION update rule is implicit when a foreign key is specified. NO ACTION means that a non-null update value of a foreign key must match some value of the parent key of the parent table when the update statement is completed. The value of a composite foreign key is null if any component of the value is null. Delete rule The delete rule of a referential constraint is specified when the referential constraint is defined. The choices are NO ACTION, RESTRICT, CASCADE, or SET NULL. SET NULL can be specified only if some column of the foreign key allows null values. The delete rule of a referential constraint applies when a row of the parent table is deleted. More precisely, the rule applies when a row of the parent table is the object of a delete or propagated delete operation (defined below), and that row has dependents in the dependent table of the referential constraint. Consider an example where P is the parent table, D is the Referential constraints Chapter 1. Concepts 11
Delete rule dependent table, and p is a parent row that is the object of a delete or propagated delete operation. The delete rule works as follows With reStRiCt or No action, an error occurs and no rows are deleted With CASCADE, the delete operation is propagated to the dependents of p in table d With SET NULL, each nullable column of the foreign key of each dependent of p in table D is set to null Each referential constraint in which a table is a parent has its own delete rule, and all applicable delete rules are used to determine the result of a delete operation. Thus, a row cannot be deleted if it has dependents in a referential constraint with a delete rule of RESTRiCt or NO ACTIon or the deletio cascades to any of its descendents that are dependents in a referential constraint with the delete rule of restrict or no action The deletion of a row from parent table P involves other tables and can affect rows of these tables If table D is a dependent of P and the delete rule is REStRiCt or NO ACTION, then d is involved in the operation but is not affected by the operation. If D is a dependent of P and the delete rule is SET NULL, then D is involved in the operation, and rows of D can be updated during the operation. If D is a dependent of P and the delete rule is CASCADE, then D is involved in the operation and rows of D can be deleted during the If rows of D are deleted, then the delete operation on P is said to be propagated to D. If D is also a parent table, then the actions described in this list apply, in turn, to the dependents of D Any table that can be involved in a delete operation on P is said to be delete-connected to P. Thus, a table is delete-connected to table p if it is a dependent of P, or a dependent of a table to which delete operations from P cascade Table check constraints a table check constraint is a rule that specifies the values allowed in one or more columns of every row in a table. A constraint is optional, and can be defined using the CREATE TABLE or the ALTER TABLE statement Specifying table check constraints is done through a restricted form of a search condition One of the restrictions is that a column name in a table check constraint on table t must identify a column of table t 2 SQL Refe Volume 1
dependent table, and p is a parent row that is the object of a delete or propagated delete operation. The delete rule works as follows: v With RESTRICT or NO ACTION, an error occurs and no rows are deleted. v With CASCADE, the delete operation is propagated to the dependents of p in table D. v With SET NULL, each nullable column of the foreign key of each dependent of p in table D is set to null. Each referential constraint in which a table is a parent has its own delete rule, and all applicable delete rules are used to determine the result of a delete operation. Thus, a row cannot be deleted if it has dependents in a referential constraint with a delete rule of RESTRICT or NO ACTION, or the deletion cascades to any of its descendents that are dependents in a referential constraint with the delete rule of RESTRICT or NO ACTION. The deletion of a row from parent table P involves other tables and can affect rows of these tables: v If table D is a dependent of P and the delete rule is RESTRICT or NO ACTION, then D is involved in the operation but is not affected by the operation. v If D is a dependent of P and the delete rule is SET NULL, then D is involved in the operation, and rows of D can be updated during the operation. v If D is a dependent of P and the delete rule is CASCADE, then D is involved in the operation and rows of D can be deleted during the operation. If rows of D are deleted, then the delete operation on P is said to be propagated to D. If D is also a parent table, then the actions described in this list apply, in turn, to the dependents of D. Any table that can be involved in a delete operation on P is said to be delete-connected to P. Thus, a table is delete-connected to table P if it is a dependent of P, or a dependent of a table to which delete operations from P cascade. Table check constraints A table check constraint is a rule that specifies the values allowed in one or more columns of every row in a table. A constraint is optional, and can be defined using the CREATE TABLE or the ALTER TABLE statement. Specifying table check constraints is done through a restricted form of a search condition. One of the restrictions is that a column name in a table check constraint on table T must identify a column of table T. Delete rule 12 SQL Reference, Volume 1
Table check constraints a table can have an arbitrary number of table check constraints. A table check constraint is enforced by applying its search condition to each row that is inserted or updated. An error occurs if the result of the search condition is false for any row When one or more table check constraints is defined in the ALTER table the new condition before the ALTER TABLE statement completes. The SET INTEGRITY statement can be used to put the table in check pending state, which allows the aLter table statement to proceed without checking the Related reference: SET INTEGRITY statement"in the SQL Reference, Volume 2 Appendix I, "Interaction of triggers and constraints"on page 829 lation levels The isolation level associated with an application process defines the degree of isolation of that application process from other concurrently executing application processes. The isolation level of an application process therefore specifies The degree to which the rows read and updated by the application are available to other concurrently executing application processes The degree to which the update activity of other concurrently executing application processes can affect the application The isolation level is specified as an attribute of a package and applies to the application processes that use the package. The isolation level is specified in the program preparation process. Depending on the type of lock, this limits or prevents access to the data by concurrent application processes. Declared temporary tables and their rows cannot be locked because they are only accessible to the application that declared them.) The database manager supports three general categories of locks Share Limits concurrent application processes to read-only operations on the dat Update Limits concurrent application processes to read-only operations on the data, if these processes have not declared that they might update the row. The database manager assumes that the process currently looking at a row may update it
A table can have an arbitrary number of table check constraints. A table check constraint is enforced by applying its search condition to each row that is inserted or updated. An error occurs if the result of the search condition is false for any row. When one or more table check constraints is defined in the ALTER TABLE statement for a table with existing data, the existing data is checked against the new condition before the ALTER TABLE statement completes. The SET INTEGRITY statement can be used to put the table in check pending state, which allows the ALTER TABLE statement to proceed without checking the data. Related reference: v “SET INTEGRITY statement” in the SQL Reference, Volume 2 v Appendix I, “Interaction of triggers and constraints” on page 829 Isolation levels The isolation level associated with an application process defines the degree of isolation of that application process from other concurrently executing application processes. The isolation level of an application process therefore specifies: v The degree to which the rows read and updated by the application are available to other concurrently executing application processes. v The degree to which the update activity of other concurrently executing application processes can affect the application. The isolation level is specified as an attribute of a package and applies to the application processes that use the package. The isolation level is specified in the program preparation process. Depending on the type of lock, this limits or prevents access to the data by concurrent application processes. (Declared temporary tables and their rows cannot be locked because they are only accessible to the application that declared them.) The database manager supports three general categories of locks: Share Limits concurrent application processes to read-only operations on the data. Update Limits concurrent application processes to read-only operations on the data, if these processes have not declared that they might update the row. The database manager assumes that the process currently looking at a row may update it. Table check constraints Chapter 1. Concepts 13
Isolation levels Exclusive Prevents concurrent application processes from accessing the data in es not apply to application processes with an isolation level of uncommitted read, which can read but not modify the data Locking occurs at the base table row. The database manager, however,can replace multiple row locks with a single table lock. This is called lock escalation. An application process is guaranteed at least the minimum requested lock level The DB2 Universal Database database manager supports four isolation levels. Regardless of the isolation level, the database manager places exclusive locks on every row that is inserted, updated, or deleted. Thus, all isolation levels ensure that any row that is changed by this application process during a unit of work is not changed by any other application processes until the unit of work is complete. The isolation levels are Repeatable Read(RR) This level ensures that. Any row read during a unit of work is not changed by other application processes until the unit of work is complete. The rows are read in the same unit of work as the corresponding Open statement. Use of the optional WITH RELEASE clause on the CLOSE statement means that any guarantees against non-repeatable reads and phantom reads no longer apply to any previously accessed rows if the cursor is reopened nother application process cannot be read until it is committed by that application process The Repeatable Read level does not allow phantom rows to be viewed(see Read Stability) In addition to any exclusive locks, an application process running at the rr level acquires at least share locks on all the rows it references. Furthermore, the locking is performed so that the application process is completely isolated from the effects of concurrent application processes Read Stability(rS) Like the Repeatable Read level, the Read Stability level ensures that Any row read during a unit of work is not changed by other application processes until the unit of work is complete. The rows are read in the same unit of work as the corresponding OPEN statement. Use of the optional WITH RELEASE clause on the CLOSE statement means that any guarantees against non-repeatable reads no longer apply to any previously accessed rows if the cursor is reopened Any row changed by another application process cannot be read until it is committed by that application process. 14 SQL Reference, Volume 1
Exclusive Prevents concurrent application processes from accessing the data in any way. Does not apply to application processes with an isolation level of uncommitted read, which can read but not modify the data. Locking occurs at the base table row. The database manager, however, can replace multiple row locks with a single table lock. This is called lock escalation. An application process is guaranteed at least the minimum requested lock level. The DB2® Universal Database database manager supports four isolation levels. Regardless of the isolation level, the database manager places exclusive locks on every row that is inserted, updated, or deleted. Thus, all isolation levels ensure that any row that is changed by this application process during a unit of work is not changed by any other application processes until the unit of work is complete. The isolation levels are: v Repeatable Read (RR) This level ensures that: – Any row read during a unit of work is not changed by other application processes until the unit of work is complete. The rows are read in the same unit of work as the corresponding OPEN statement. Use of the optional WITH RELEASE clause on the CLOSE statement means that any guarantees against non-repeatable reads and phantom reads no longer apply to any previously accessed rows if the cursor is reopened. – Any row changed by another application process cannot be read until it is committed by that application process. The Repeatable Read level does not allow phantom rows to be viewed (see Read Stability). In addition to any exclusive locks, an application process running at the RR level acquires at least share locks on all the rows it references. Furthermore, the locking is performed so that the application process is completely isolated from the effects of concurrent application processes. v Read Stability (RS) Like the Repeatable Read level, the Read Stability level ensures that: – Any row read during a unit of work is not changed by other application processes until the unit of work is complete. The rows are read in the same unit of work as the corresponding OPEN statement. Use of the optional WITH RELEASE clause on the CLOSE statement means that any guarantees against non-repeatable reads no longer apply to any previously accessed rows if the cursor is reopened. – Any row changed by another application process cannot be read until it is committed by that application process. Isolation levels 14 SQL Reference, Volume 1