SQL Programming Manual for Pascal

HP NonStop SQL Programming Manual for Pascal—528614-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.
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
table. You should ensure that these files are explicitly SQL compiled to avoid automatic
recompilation every time a program runs.
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.
Note. When you add a constraint, NonStop SQL checks all rows in the table. For large tables,
the CREATE CONSTRAINT operation might run for a long time.