Access
To create, modify or display the general characteristics of an integrity constraint assigned to a logical entity, select the corresponding context-sensitive menu item (or equivalent button) from the Logical entity integrity constraints dialog box.
This dialog box displays the description of an integrity constraint.
Data entry
Name of the constraint
Enter the name of the integrity constraint into this field. This is a SQL name (128 characters) that must be unique in the repository.
Mandatory entry.
Check boxes
Extended syntax
Checked |
Constraint name input control is disabled (lower case, special characters and spaces allowed). |
Unchecked |
The constraint's name must conform to Adelia naming conventions. |
By default: unchecked.
Constraint enabled
Checked |
The constraint is enabled, i.e. it is taken into account when generating the PDM. If a constraint has an invalid definition, it is automatically disabled. Note: during backup, for foreign key type constraints, if the constraint is enabled, you will be asked if necessary to enable the corresponding unique key constraint. |
By default: box checked for manually created constraints, unchecked for automatically proposed constraints.
Lists
Constraint type
There are three types of constraint: Unique key, Foreign key, Check.
Mandatory entry.
Check constraints
Data entry
Constraint text
Free-form constraint text. This text is not checked, only property names are substituted.
During the generation procedure, the text is substituted using a rule analogous to level 2 generation: $PROPERTY identifiers (PROPERTY being a logical entity property name) are substituted by guide word or DDS property name, depending on the generation options defined.
Unique key constraints
Lists
Key fields
This list displays the entity's fields that are part of the unique key. The + and - buttons are used to add a new field, or to delete the selected fields.
Click on a field to display a drop-down list used to select the entity's logical properties.
Foreign key constraints
Data entry
Origin entity
This field indicates the entity referenced by the foreign key. The data is entered via a selection box, displayed by clicking the "..." button next to the entry field.
The box offers logical entities with a unique key identifier, generated with a key (i.e. having a primary key as defined for SQL) or having at least one unique constraint defined.
Lists
ON UPDATE trigger
Used to specify the action to perform when the referenced unique key is modified in the parent table.
The possible values are as follows:
NO ACTION |
The parent table is not updated if there is a record with the same key value in the dependent table. |
RESTRICT |
The parent table is not updated if there is a record with the same key value in the dependent table, but an immediate check is made to prevent any other constraints (or triggers) being activated in the event of an integrity exception. |
CASCADE |
When a record in the parent table is updated, any records with the same key value in the dependent table is also updated. All the "key" fields in any such records are updated with the new value of the corresponding key from the parent table. |
SET NULL |
When a record in the parent table is updated, any records with the same key value in the dependent table is also updated. All the "key" fields in any such records that can take a "null" value are updated with the null value. |
SET DEFAULT |
When a record in the parent table is updated, any records with the same key value in the dependent table is also updated. All the "key" fields in any such records are updated with the column's default value. |
By default : NO ACTION.
ON DELETE trigger
Used to specify the action to perform when the referenced unique key is deleted from the parent table.
The possible values are as follows:
NO ACTION |
The record is not deleted from the parent table if there is a record with the same key value in the dependent table. |
RESTRICT |
The record is not deleted from the parent table if there is a record with the same key value in the dependent table, but an immediate check is made to prevent any other constraints (or triggers) being activated in the event of an integrity exception. |
CASCADE |
When a record in the parent table is deleted, any records with the same key value in the dependent table are also deleted. |
SET NULL |
When a record in the parent table is deleted, any records with the same key value in the dependent table are also updated. All the "key" fields in any such records that can take a "null" value are updated with the null value. |
SET DEFAULT |
When a record in the parent table is deleted, any records with the same key value in the dependent table are also updated. All the "key" fields in any such records are updated with the column's default value. |
By default: NO ACTION.
Key fields
This list displays the entity fields included in the key (Key column), along with the corresponding unique key fields (Reference column) in the referenced entity.
The + and - buttons are used to add a new field, or to delete the selected fields.
Click on a field to display a drop-down list used to select the entity's logical properties. When entering the reference, only compatible fields are displayed.
Notes on update and deletion rules (ON UPDATE and ON DELETE):
Not all available options are necessarily supported by all database management systems.
- If the database management system does not support the RESTRICT option, this latter is silently replaced by NO ACTION when generating the constraint.
- If the database management system does not support the SET DEFAULT option, this latter is silently replaced by SET NULL when generating the constraint.
- For all other errors, generation fails with a code indicating that the function is not supported.
The possible combinations are shown in the following tables:
ON UPDATE
Access |
DB2 |
Oracle |
SQL Server |
MySQL |
PostgreSQL |
AS/400 |
|
NO ACTION |
Yes |
Yes |
Yes |
Yes |
Yes |
Yes |
Yes |
CASCADE |
Yes |
- |
- |
Yes |
Yes |
Yes |
- |
RESTRICT |
- |
Yes |
- |
- |
Yes |
Yes |
Yes |
SET NULL |
- |
- |
- |
Yes |
Yes |
Yes |
- |
SET DEFAULT |
- |
- |
- |
Yes |
- |
Yes |
- |
ON DELETE
Access |
DB2 |
Oracle |
SQL Server |
MySQL |
PostgreSQL |
AS/400 |
|
NO ACTION |
Yes |
Yes |
Yes |
Yes |
Yes |
Yes |
Yes |
CASCADE |
Yes |
Yes |
Yes |
Yes |
Yes |
Yes |
Yes |
RESTRICT |
- |
Yes |
- |
- |
Yes |
Yes |
Yes |
SET NULL |
Yes |
Yes |
Yes |
Yes |
Yes |
Yes |
Yes |
SET DEFAULT |
- |
- |
- |
Yes |
- |
Yes |
Yes |
Buttons
Enter/Create/Modify (validation)
The Enter button (in display mode) closes the dialog box.
The Create button (in creation mode) or Modify button (in modification mode) validates the dialog box.
The various details in the box are checked. If all are valid, the integrity constraint definition is updated in the database before closing the dialog box; if not, an error message is displayed.
Cancel
This button closes the dialog box without validating the choices made.
The processes run on integrity constraints, however, are not cancelled.
Keyboard shortcut: Esc.