Referential Integrity Management on Informix
The INF connector allows either Uniface or Informix to handle referential integrity. The default is Uniface.
The INF connector supports referential integrity for Informix with logging only. If no logging is used, you are advised to let Uniface handle referential integrity.
If referential integrity is handled by Informix, the mapping of Uniface relationships and foreign key rules to Informix is accomplished by using Informix referential integrity constraints, triggers, and the scripts generated by the Uniface Create Script utility.
Use the Uniface Create Script facility to create Informix SQL script files which can:
- Create DBMS referential integrity controls.
- Drop DBMS referential integrity controls.
- Verify referential integrity by checking for unlinked foreign keys which are not null.
For more information, see Create Script Facility and /genSql.
Uniface Referential Integrity Mapping to Informix
The different types of referential integrity are shown in the table:
| Foreign key rule | Actions to be controlled | Explanation |
|---|---|---|
| RES | Restrictive delete on the one table. | Users are not allowed to delete a key in one table if the key is linked to a foreign key. |
| Restrictive update on the one table. | Users are not allowed to change the value of a key on the one table if the original value is linked to a foreign key. | |
| Restrictive update on the many table. | Users are not allowed to update a foreign key unless the new value is null or has a matching key in the one table. | |
| Restrictive insert on the many table. | Users are not allowed to insert a foreign key unless the foreign key is null or there is a matching key in the one table. | |
| CAS | Cascading delete on the one table. | If a key in the one table is linked to one or more foreign keys, all foreign keys which are linked to the deleted key are removed from the many table. |
| Restrictive update on the one table. | Users are not allowed to change the value of a key on the one table if the original value is linked to a foreign key. | |
| Restrictive update on the many table. | Users are not allowed to update a foreign key unless the new value is null or has a matching key in the one table. | |
| Restrictive insert on the many table. | Users are not allowed to insert a foreign key unless the foreign key is null or there is a matching key in the one table. | |
| NUL | Nullify delete on the one table. | If a key in the one table is linked to one or more foreign keys, all foreign keys which are linked to the deleted key are set to null. |
| Restrictive update on the one table. | Users are not allowed to change the value of a key on the one table if the original value is linked to a foreign key. | |
| Restrictive update on the many table. | Users are not allowed to update a foreign key unless the new value is null or has a matching key in the one table. | |
| Restrictive insert on the many table. | Users are not allowed to insert a foreign key unless the foreign key is null or there is a matching key in the one table. |
Uniface Foreign Key Rules Mapped to Informix
Uniface foreign key rules are mapped to Informix as shown in the table:
|
Foreign key rule |
Informix |
|---|---|
|
RES |
Referential integrity constraints. |
|
CAS |
Referential integrity constraints and triggers. |
|
NUL |
Referential integrity constraints and triggers. |
Caution: When the foreign key contains fields that are defined to be mandatory in Uniface (not nullable in Informix), the nullify delete returns an error and the delete is rolled back. This causes the delete to be a restrictive delete.
Referential Integrity Constraints
Primary, candidate (unique), foreign keys, and relationships between tables are defined by means of referential integrity constraints. They are used to enforce the following:
- Restrictive delete on the one table
- Restrictive update on the one table
- Restrictive update on the many table
- Restrictive insert on the many table
Uniface uses the name of the overflow table as the name of the relation between a table and its overflow table.
Caution: There must be a one-to-one correspondence between the data types of the primary and foreign keys. Referential integrity constraints cannot be placed on a Byte or Text column.
Triggers
Triggers are used to enforce referential integrity in the following ways:
- Nullify delete on the one table
- Cascading delete on the one table
When a table is involved in multiple relationships with a CAS or NUL foreign key rule, multiple actions are combined in the triggers. The name of the relationship is included in a comment with every action.
Consider the following:
- You must be either the owner of the table or have database administrator (DBA) status to create a trigger on a table.
- Triggers can only be created on a table in the current database.
- Triggers cannot be created on temporary tables, views, or system catalog tables.
- The statements inside the triggered action can affect tables in external databases.
- If a trigger fails and the database has transactions, all triggered actions and the triggering statement are rolled back. The rest of the transaction, however, is not rolled back.
