Products Downloads


French version


 

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

AS/400

NO ACTION

Yes

Yes

Yes

Yes

Yes

Yes

CASCADE

Yes

-

-

Yes

Yes

-

RESTRICT

-

Yes

-

-

Yes

Yes

SET NULL

-

-

-

Yes

Yes

-

SET DEFAULT

-

-

-

Yes

-

-

 

ON DELETE

 

Access

DB2

Oracle

SQL Server

MySQL

AS/400

NO ACTION

Yes

Yes

Yes

Yes

Yes

Yes

CASCADE

Yes

Yes

Yes

Yes

Yes

Yes

RESTRICT

-

Yes

-

-

Yes

Yes

SET NULL

Yes

Yes

Yes

Yes

Yes

Yes

SET DEFAULT

-

-

-

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.

 

 

 

 

 

 

 

↑ Top of page

  • Aucune étiquette