Example: Ensuring data integrity with referential constraints

 

Suppose that a database contains an employee file and a department file. You can define a referential constraint to ensure that every employee in the employee file belongs to a corresponding department in the department file.

Both the employee and department files have a department number field named DEPTNO. The related records of these database files are those for which employee.DEPTNO equals department.DEPTNO.

1. Use the Add Physical File Constraint (ADDPFCST) command, add a primary key constraint or a unique constraint to the department file for the DEPTNO field. This primary key or unique constraint will later become a parent key. It is not yet a parent key because a referential constraint has not yet been added.

2. Add a referential constraint to the employee file using the ADDPFCST command. The employee file will be the dependent file. The foreign key will be employee.DEPTNO. The department file will be the parent file with parent key department.DEPTNO. Because there is either a primary key constraint or a unique constraint with the DEPTNO field as the key, the constraint will serve as the parent key associated with the referential constraint.

The referential constraint has update and delete rules that must be followed for record insert, update, and delete operations on the parent or the dependent file.

 

Parent topic:

Ensuring data integrity with referential constraints