SQL Programming Manual for Pascal

Enforcing Data Integrity
HP NonStop SQL Programming Manual for Pascal—528614-001
E-3
Examples
In this example, every department must report to another valid department in the table.
To verify this rule, you can use the following SELECT statement to check the DEPT
table:
SELECT DEPTNUM, RPTDEPT
FROM DEPT
WHERE RPTDEPT
NOT IN (SELECT DEPTNUM FROM DEPT) ;
The query returns the following 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 the following statement:
UPDATE DEPT SET RPTDEPT = 1120
WHERE DEPTNUM = 1320;
--- 1 row(s) updated.
Repeating the previous SELECT verifies the correction.
In the next 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. The following 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) ;
The query returns the following result:
CLASSNUM CLASS_COURSE
-------- ------------
--- 0 row(s) selected.
The result of zero rows selected indicates that each class points to a valid course. The
result does not prove that the class points to the correct course.