SQL Programming Manual for Pascal

Enforcing Data Integrity
HP NonStop SQL Programming Manual for Pascal—528614-001
E-2
Managing Referential Integrity
Constraints are also a replacement for program code; they operate for all programs
that refer to a table to which constraints apply. For example, constraints can be used to
establish value ranges for columns, true or false conditions, and so forth.
Consider the following example:
CREATE CONSTRAINT MGRNUM_CONST
ON DEPT
CHECK MANAGER BETWEEN 0001 AND 5000 ;
This constraint on the DEPT table restricts the value of the column MANAGER to the
range shown. Your programs do not have to check the value for each insertion or
update to this table. SQL ensures the value is within the range. If the value is not within
the range, SQL returns an error message and aborts any current TMF transaction. This
is a database protection mechanism. When constraints exist, programs do not have to
perform such checks to avoid corrupting the database.
The following example illustrates a constraint that examines two columns within the
same row:
CREATE CONSTRAINT VALIDATE_CONST
ON EMPLOYEE
CHECK TERM_DATE >= HIRE_DATE ;
This constraint ensures that the employee termination date is equal to or greater than
the date of hire.
Managing Referential Integrity
Referential integrity is a user test to ensure that any foreign key values that exist in a
table also exist as primary key values in the same or another table. A foreign key is a
column in a table that is a primary key in that table or in another table. The rule for
referential integrity is that every foreign key value must have a corresponding primary
key value.
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 can check for referential integrity through a program or with SQLCI.
Examples
The following examples use SQLCI to illustrate methods for checking and maintaining
referential integrity.