Foreign Keys

A foreign key establishes a link between two tables, namely between a 'foreign key table' and a 'reference table'. When the data in the reference table is changed or deleted, specific actions can automatically be done to the linked data on the foreign key table.

The link is established between the keys of the primary index (or the built-in LIONRECNO index) on the 'reference table' and specific fields on the 'foreign key table'. A table can have multiple foreign keys defined, thereby linking it to multiple reference tables.

Creating a foreign key

To create a new foreign key, do the following:

Open the table definition on which you want to define the foreign keys and select the "Foreign Keys" node.
Ensure that the table is locked by selecting the lock button in the top toolbar.
Select the option "Add new foreign key" from the right-hand pane. The following screen appears:
To complete the specification, see "Adjusting a foreign key" below.

Adjusting a foreign key

Select the foreign key you want to adjust from the "Foreign Keys" node.
Ensure that the table is locked by selecting the lock button in the top toolbar.
Set the properties for the foreign key as described below:

Option:	Description:	Value:
Name	Name of the foreign key.	<Text>
Description	Description of the foreign key.	<Text>
Reference table	The table to which the foreign key references. This table can be picked from the dropdown list, which contains all tables that are available in the application.	<Table name>
Reference index	The index that is related the reference table. The dropdown list only contains indexes having unique keys.	<Index name>
ON DELETE	The action to be performed on the foreign key table when the associated record is deleted from the reference table. 'Cascade' - will delete the reference table record and the associated foreign key record. 'No Action' -The reference table record and foreign key record will not be deleted. 'Set NULL' - will delete the reference table record and set the associated foreign keys to NULL values . The Set null option is only supported for Oracle, DB2 and MS-SQL2005.	<Cascade \| No Action \| Set null>
ON UPDATE	The action to be performed on the foreign key table when the associated record is updated on the reference table. 'No Action' -The reference table keys and associated foreign keys will not be updated. 'Cascade' - the reference table keys and associated foreign keys will be updated. Attention: The cascade option only applies to MS-SQL 2000 and later. This means that if you are using another database management system, like Oracle or DB2, this option is not supported.	<Cascade \| No Action>
Table fields	The fields that are available in the current table.	<Field name>
Reference fields	Key fields in the Reference index. Select from a dropdown list the key field from the reference index to associate with the field on the current table. NOTE : - ensure that all reference fields have been associated to a field on the current table. - the type, length, etc of a reference field must be the same as that of the foreign key field that it is associated to, otherwise an error will be produced at generation time.	<Field name>

On completion, click the save button in the top toolbar of the Developer.
The table must be checked in first before it can be called from other objects.

Deleting a foreign key

Open the table definition on which you want to adjust the foreign keys and select the "Foreign Keys" node.
Ensure that the table is locked by selecting the lock button in the top toolbar.
Select the foreign key to delete and right-click on it, then select "Delete" from the pop-up menu (or press Delete on the keyboard).
On completion, click the save button in the top toolbar of the Developer.
The table must be checked in first before it can be called from other objects.

Generate the application and reorganize the database

After a foreign key is added, adjusted or deleted, a Whole System generate is required, followed by a database reorganize.