Reference property sheet Integrity tab

Referential integrity is a collection of rules that govern data consistency between primary keys, alternate keys and foreign keys. It dictates what happens when you update or delete a value in a referenced column in the parent table, and when you delete a row containing a referenced column from the parent table.

The Integrity tab contains the following properties:

Property	Description
Constraint name	Name of the referential integrity constraint. Maximum length is 254 characters
Implementation	Specifies how referential integrity will be implemented. You can choose between: Declarative- Referential integrity constraints are defined for particular references. When the reference is generated the target DBMS evaluates the reference validity and generates appropriate error messages Trigger - Referential integrity constraints are implemented by triggers based on the integrity constraints defined in the reference property sheet. The trigger evaluates reference validity and generates appropriate user-defined error messages
Cardinality	Indicates the minimum and maximum number of instances in a child table permitted for each corresponding instance in the parent table. The following values are available by default: 0..* - A parent can have zero or more children. There is no maximum. 0..1 - A parent can have zero or one children. 1..* - A parent can have one or more children. There is no maximum. 1..1 – A parent must have exactly one child Alternately, you can enter your own integer values in one of the following formats: x..y - A parent can have between x and y children. x - A parent can have exactly x children. x..y, a..b - A parent can have between x and y or between a and b children. You can use * or n to represent no limit. Examples: 2..n – There must be at least 2 children. 10 - There must be exactly 10 children. 1..2, 4..n – There must be one, two, four or more children.
User-defined	Indicates a user-defined constraint name
Update constraint	How updating a key value, in the parent table affects the foreign key value in the child table. Depending on the implementation and DBMS, you can choose between: None - Update or deletion of a value in the parent table has no effect on the child table. Restrict - A value in the parent table cannot be updated or deleted if one or more matching child values exists Cascade - Update or deletion of a value in the parent table causes an update or delete of matching values in the child table Set null - Update or deletion of a value in the parent table sets matching values in the child table to NULL Set default - Update or deletion of a value in the parent table sets matching values in the child table to the default value
Delete constraint	How deleting a row in the parent table affects the child table
Mandatory parent	Each foreign key value in the child table must have a corresponding key value, in the parent table
Change parent allowed	A foreign key value can change to select another value in the referenced key in the parent table
Check on commit	[Sybase SQL Anywhere 5.0 and 5.5 only] Verifies referential integrity only on the commit, instead of verifying it after row insertion. You can use this feature to control circular dependencies
Cluster	Indicates whether the reference constraint is a clustered constraint (for those DBMS that support clustered indexes)

Property

Description

Constraint name

Name of the referential integrity constraint. Maximum length is 254 characters

Implementation

Specifies how referential integrity will be implemented. You can choose between:

Declarative- Referential integrity constraints are defined for particular references. When the reference is generated the target DBMS evaluates the reference validity and generates appropriate error messages
Trigger - Referential integrity constraints are implemented by triggers based on the integrity constraints defined in the reference property sheet. The trigger evaluates reference validity and generates appropriate user-defined error messages

Cardinality

Indicates the minimum and maximum number of instances in a child table permitted for each corresponding instance in the parent table. The following values are available by default:

0..* - A parent can have zero or more children. There is no maximum.
0..1 - A parent can have zero or one children.
1..* - A parent can have one or more children. There is no maximum.
1..1 – A parent must have exactly one child

Alternately, you can enter your own integer values in one of the following formats:

x..y - A parent can have between x and y children.
x - A parent can have exactly x children.
x..y, a..b - A parent can have between x and y or between a and b children.

You can use * or n to represent no limit.

Examples:

2..n – There must be at least 2 children.
10 - There must be exactly 10 children.
1..2, 4..n – There must be one, two, four or more children.

User-defined

Indicates a user-defined constraint name

Update constraint

How updating a key value, in the parent table affects the foreign key value in the child table. Depending on the implementation and DBMS, you can choose between:

None - Update or deletion of a value in the parent table has no effect on the child table.
Restrict - A value in the parent table cannot be updated or deleted if one or more matching child values exists
Cascade - Update or deletion of a value in the parent table causes an update or delete of matching values in the child table
Set null - Update or deletion of a value in the parent table sets matching values in the child table to NULL
Set default - Update or deletion of a value in the parent table sets matching values in the child table to the default value

Delete constraint

How deleting a row in the parent table affects the child table

Mandatory parent

Each foreign key value in the child table must have a corresponding key value, in the parent table

Change parent allowed

A foreign key value can change to select another value in the referenced key in the parent table

Check on commit

[Sybase SQL Anywhere 5.0 and 5.5 only] Verifies referential integrity only on the commit, instead of verifying it after row insertion. You can use this feature to control circular dependencies

Cluster

Indicates whether the reference constraint is a clustered constraint (for those DBMS that support clustered indexes)

Implementing referential integrity
In PowerDesigner 6, the implementation of referential integrity was defined as a generation option. However, in PowerDesigner 8, you indicate the implementation of referential integrity using declarative referential integrity, or by the use of triggers, from the Integrity tab of the reference property sheet.