SQL Programming Manual for TAL

HP NonStop SQL Programming Manual for TAL—527887-001
E-1
E Enforcing Data Integrity
Data integrity requires that certain data conditions must be true within a database.
Examples of these conditions are:
•
Data format, such as numeric only
•
Value ranges, such as between 500 and 1000
•
A relationship between items within a row
•
A relationship between items in different rows of a table or between rows in
different tables (Referential Integrity)
The data format is controlled by the data type definition in the CREATE TABLE
statement. NonStop SQL implicitly checks the data type whenever data is added to or
updated in a table. For example, if a column is defined as numeric, the insertion of a
character string is not allowed.
Value ranges and relationships with other columns within the same row are controlled
by constraints. (See Using Constraints
on page E-1.)
A relationship between items in different rows of a table or between rows in different
tables is called referential integrity. You can include code in your program to enforce
referential integrity.
For example, two tables exist and contain data about employees and work
departments. Inconsistencies can be introduced through coding errors, or through
operations performed with SQLCI. For example, the program should not delete a
department from the DEPT table if any row in the EMPLOYEE table refers to that
department. You should check for such inconsistencies. (See “Managing Referential
Integrity on page E-2.)
Using Constraints
NonStop SQL supports constraints to protect the integrity of base tables. A constraint
is a condition that must be met before data is added to a row in the base table to which
the condition applies.
NonStop SQL ensures that all modifications to rows satisfy all current constraints. This
means that you cannot add a row or change a row when the new data does not meet
the constraint specification. A new constraint is disallowed if any rows currently in the
table do not satisfy it.
You can create or drop constraints at any time. Creating or dropping a constraint,
however, causes the system to invalidate all SQL program files that use the underlying
Note. Logical operations, such as checking for referential integrity, should be performed within
a TMF transaction in order to ensure consistency. If the referential integrity constraint is not
satisfied, the transaction can be rolled back.