Referential integrity properties

Chapter 3 Building Physical Diagrams

Referential integrity properties

The definition of referential integrity includes the following properties:

Property	Description
Constraint name	Name of the referential integrity constraint. Maximum length is 254 characters
Implementation	Indicates whether implementation of referential integrity is declarative, or by the use of triggers
Cardinality	Indicates the maximum and minimum number of instances in a child table that can appear for each corresponding instance in the parent table
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
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*	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)

* Only available for Sybase SQL Anywhere 5.0 and 5.5.

Cardinality

Defining cardinality allows you to control the minimum and maximum number of children permitted for each parent. You can use the following values to define cardinality:

Cardinality	Minimum	Maximum
0	A parent does not have a minimum number of children. Child is optional	—
1	At least one child must exist for each parent	Only one child can exist for each parent
any integer	Minimum number is the number indicated	Maximum number is the number indicated
N or *	—	Any number of children can exist for a each parent

Cardinality format

Cardinality can be expressed in the following formats:

Format	Description	Example
x..y	Minimum and maximum interval, where x is any integer, and Y can be any of the following: Integer n (infinite) * (infinite)	(2..10) There must exist between two and ten children for each parent (0..n) Child is optional, and there can be any number of children for any one parent 10* There must exist at least ten children for each parent
y	Maximum value which is equivalent to the form (0..y)	(10) There can be up to ten children for each parent. Child is optional
x..y, x..y	Series of intervals, where the cardinality can be any one of the intervals	(1..2, 4..n) There can exist either one or two children for each parent, or at least four children for each parent

Format

Description

Example

x..y

Minimum and maximum interval, where x is any integer, and Y can be any of the following:

Integer

n (infinite)

* (infinite)

(2..10)

There must exist between two and ten children for each parent

(0..n)

Child is optional, and there can be any number of children for any one parent

10*

There must exist at least ten children for each parent

Maximum value which is equivalent to the form (0..y)

(10)

There can be up to ten children for each parent. Child is optional

x..y, x..y

Series of intervals, where the cardinality can be any one of the intervals

(1..2, 4..n)

There can exist either one or two children for each parent, or at least four children for each parent

Update and delete constraints

Update constraint and delete constraint take any of the following values:

Value	Change to parent table	Result in child table
None	Update or delete parent value	None
Restrict	Cannot update or delete parent value if one or more matching child values exist	None
Cascade	Update or delete parent value	Update or delete matching child values
Set null	Update or delete parent value	Set matching child values to NULL
Set default	Update or delete parent value	Set matching child values to default value