Referential Integrity
Referential integrity is a standard feature of relational database design that prevents users or applications from entering inconsistent data. Most relational databases have referential integrity rules that you can apply when you create a relationship between tables.
The following rules apply to all databases:
- A primary key is a column, or group of columns, that uniquely identifies each record in a table. A table can have only one primary key and the primary key cannot be null.
- A foreign key is the field (or fields) of the many table in a relationship. It refers to the primary key of the one table in the relationship. A foreign key can be null but it always refers to an existing primary key value.
Referential integrity prevents occurrences from being added to a many entity if there is no related occurrence of the one entity, and it ensures that if an occurrence of the one entity is deleted, the related occurrences in the many entity are also deleted.
For example, if table Y is the many entity in a relationship with table X, table Y has a foreign key that points to a field in table X. A referential integrity rule can prevent you from adding data to table Y that cannot be linked to data in table X. Referential integrity also dictates that if you delete a record from table X, any records that are linked to the deleted record are also deleted.
Uniface and Referential Integrity Checking
Uniface checks for referential integrity in a component only if one entity is nested within the other entity of a relationship in the component data structure. The many entity can be an outer entity or an inner entity. When entity services are used in an application, the referential integrity must be centralized in the entity service of the one entity. Delete constraints are not applied if the many entity is not included in the component data structure of the entity service.
Referential integrity constraints relate to the way in which you can delete an occurrence of the one entity. The following constraints are possible when deleting occurrences:
- Cascade—primary key and foreign key both deleted
- Restricted—primary key deleted only if there is no matching foreign key
- Nullify—primary key deleted and foreign key set to null
Delete a race track, all its associated races are also deleted.
It is not possible to delete a race track as long as it has at least on associated race.
Delete a race track, but protect its associated races.
Centralizing referential integrity in the entity service eliminates the need to use the many entity in all components that use the one entity. It also increases the effectiveness of referential integrity checking between entities.
Uniface vs. Database Referential Integrity
By default, Uniface handles referential integrity checking. For some databases, it is possible for the database itself to handle referential integrity checking.
DBMS connectors that support referential integrity in the database use a connector option to disable or enable Uniface referential integrity. The name of this option is DBMS-specific and the default state depends on the DBMS connector used. See the relevant documentation for the options available for your DBMS connector.
Note: Referential integrity should be maintained either in Uniface, or in the database. Implementing referential integrity in both Uniface and the database has a negative impact on performance.
If you implement an application that uses multiple DBMS connectors, you must configure all DBMS connectors that you use for Uniface referential integrity support. This is because every DBMS connector enables or disables the Uniface implementation of referential integrity for all tables (including Uniface system tables) accessed by that connector.
