Chapter 3 Building Physical Diagrams

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)

 

Copyright (C) 2007. Sybase Inc. All rights reserved.