Integrity constraints

Uniqueness constraint

Definition: Each time an n-tuple is created, the uniqueness constraint makes sure the value of the primary key does not already exist.

The preservation of the uniqueness of a table's primary key is an integrity constraint which has already been dealt with using a "unique index".

Referential integrity

Definition: Inter-table constraint, which consists of the imposition that the value of a group of attributes of a first table should appear as key value in a second table.

This integrity constraint, which generally only applies to associations, specifies that an association instance can only exist if the entities which take part in this association already exist. It is set up through the re-definition of outside keys that have an impact on the updating operations. When an association element is inserted, it is necessary to check that the referenced elements do exist. Similarly, when a referenced element is deleted, it is necessary to check that the element no longer exists in the association (or, depending on the case, to destroy the corresponding association instance).

Example

Figure 52. Example of associations with integrity control

The mapping of the "belong" and "localization" associations introduces several reference integrity constraints:

· any value of the HIS_SERVICE attribute appearing in the EMPLOYEE table should be described in the SERVICE table

· any tuple of the BUILDING_LOCALIZATION_SERVICE and SERVICE_LOCALIZATION_BUILDING, must reference a tuple of the BUILDING table and a tuple of the SERVICE table

Consequences

Referential integrity constraints are used to check consistency during extraction from and insertion into the table that represents the association.

A ban is imposed during the deletion of an instance referenced by an outside key, or during insertion into a table representing an association of keys which references instances which do not exist. The implementation of these constraints depends on the RDBMS used.
The effect of the integrity constraints can be modified using tagged values specific to the RDBMS, such as {onDeleteCascade}.

Generalization and constraints

Between classes which have children, constraint generation on association tables differs according to the chosen generalization mode:

· one table per concrete class

· one table per class

One table per concrete class

Figure 53. One table per concrete class

The SQL tables generated are as follows (Figure 54):

Figure 54. The SQL tables generated with one table per concrete class

One table per class

Figure 55. One table per class

The SQL tables generated are as follows (Figure 56):

Figure 56. The SQL tables generated with one table per class

"Not null" attributes

In addition to the "not null" deduced from the model (constraints on keys, association multiplicity ...), the user may define locally that a particular attribute must always be entered in a database, using the <<notNull>> constraint.

Integrity tagged values

The code generated ensures the database is complete, but if it must be used by other sources, specific reflexes have to be added, such as on delete cascade (see the appendix specific to the RDBMS used).