SQL Programming Manual for TAL

Enforcing Data Integrity
HP NonStop SQL Programming Manual for TAL—527887-001
E-2
Managing Referential Integrity
table. You should ensure that these files are explicitly SQL compiled to avoid automatic
recompilation every time a program runs.
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 this 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.
This 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.
You can check for referential integrity through a program or with SQLCI.
These examples use SQLCI to illustrate methods for checking and maintaining
referential integrity.
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.