This section shows how to use a Lookup rule to implement referential integrity rules between two tables, xDepartments and xEmployee.
•When an employee record is inserted, xDepartmentCode must exist in the xDepartments table
•If xDepartmentCode for an Employee is changed, the code must exist in table xDepartments
•Deleting an entry from table xDepartments is not allowed if employees belonging to this Department exist in table xEmployee.
1. We will first look at referential integrity in the Departments table. Open table xDepartments in the editor. On the Repository tab, expand the Tables group and locate table xDepartments. Open it using the context menu.
2. Select the Rule and Triggers tab. Select Simple Rules Lists from the toolbar View button:
3. Two rules have been defined:
•Column xDepartmentCode has a lookup rule defined in the table
•Column xDepartmentDescription has a SimpleCheck rule defined at field level.
4. Double-click the Department Code lookup rule to show its details on the Details tab.
A. Table name xEmployee is looked up using index xEmployeeByDepartment.
B. xDepartmentCode is the look up key. The keys for index xEmployeeByDepartment are shown as their Indentifier names, XDEPTMENT and XEMPLOYID.
C. The rule is applied only when deleting from table xDepartments. Note that When inserting and When updating are both set to Never Apply Rule
D. If found, an error is returned with a message.
5. Now we will look at the referential integrity checks between the Employee table and the Departments table.
Open the table xEmployee in the editor.
6. Select the Rules and Triggers tab.
7. Expand the rule for column xDepartmentCode:
The rule looks up xDepartmentCode in table xDepartments, when adding or changing an Employee record. If not found, an error is returned with a message from message file, DC@M01.
Note: When you load the definitions for your own application tables, this type of referential integrity rule can be added. This will then apply to your Visual LANSA applications which access this table.
If you define your database using the Logical Modeler tool and diagram the table relationships, these rules will be created automatically.