SQL Programming Manual for TAL

Enforcing Data Integrity
HP NonStop SQL Programming Manual for TAL—527887-001
E-3
Managing Referential Integrity
Referential Integrity: Example 1
In this example, every department must report to another valid department in the table.
To verify this rule, you can use this SELECT statement to check the DEPT table:
SELECT DEPTNUM, RPTDEPT
FROM DEPT
WHERE RPTDEPT
NOT IN (SELECT DEPTNUM FROM DEPT) ;
The query returns this result:
DEPTNUM RPTDEPT
------- -------
--- 0 row(s) selected.
The absence of selected rows indicates the integrity of the database is intact. If any
department contained an invalid report-to department, it would have been selected and
shown, for example, as:
DEPTNUM RPTDEPT
------- -------
1320 999
--- 1 row(s) selected.
Such an error could be corrected with this statement:
UPDATE DEPT SET RPTDEPT = 1120
WHERE DEPTNUM = 1320;
--- 1 row(s) updated.
Repeating the previous SELECT verifies the correction.
Referential Integrity: Example 2
In this example, the CLASS table has a primary key of CLASSNUM and a foreign key
of CLASS_COURSE--each class is one offering of the course. The COURSE table has
a primary key of COURSENUM. This code determines whether each class row does
indeed point to a valid course:
SELECT CLASSNUM, CLASS_COURSE
FROM CLASS
WHERE CLASS_COURSE
NOT IN (SELECT COURSENUM FROM COURSE) ;