A constraint declaration constrains the operations that may be performed on relations:

• Key constraint
• Attribute constraint
• General constraint

Key declarations significant:

• The assertion of the primary and and alternate (candidate) keys for a relation is also an assertion of some of the semantics (including some semantics of the entity type).
• The primary/foreign key mechanism is the main mechanism for denoting relationships between relations.

Primary keys

Declaration of a primary key of a relation is one form of constraint definition for candidate keys. Specifically a relation cannot have more than one tuple where the values of the primary keys are the same; i.e. They are always unique - "There can be only one". The primary key must always have a value. This is dictated by the entity integrity rule:

No attribute that forms part of the primary of a relation is allowed to be null.

Entity integrity must hold for all relations in relational theory.

• A tuple can only be inserted if the primary key value is unique.
• An update of a tuple cannot change an attribute forming part of the primary key to null.
• An update of a tuple cannot change an attribute forming part of the primary key to a value that would make the primary key of the updated tuple the same as the primary key of another tuple.

Alternate keys

An alternate key imposes less restrictive constraints. Specifically any relation cannot have two tuples containing the same value at the alternate key. The entity integrity rule does not apply. This is guaranteed by the primary key. An alternate key can have a value of null dependent on semantics.

• No duplicates of the alternate key are allowed.
• It is not possible to insert or update a tuple that would allow two tuples to have the same value for the alternate key.

Foreign keys and referential integrity

A foreign key is an attribute (or combination of attributes) in one relation R2 whose values match those of the primary key of some relation R1 (where R1 and R2 are not necessarily distinct). Specifically a foreign key value must always be the same as some existing value of the primary key that it references otherwise the relation is invalid. The identifiers can be different names but their values are the same.

The referential integrity rule

If a relation R2 has a foreign key F that references the primary key P in another relation R1 then every R2/F entry must either be a value equal to an R1/P primary key value or be null.

This rule has two effects:

• A constraint on the referencing relation is that no tuple may be added or updated where an attribute declared as a foreign key references a relation with no matching primary key value.
• A constraint on the referenced relation is that it may not have a tuple deleted or updated that removes a primary key that is referred to from the referencing relation.

Deletions from a referenced relation

Deleting a tuple is straight forward if:

• The primary key value of that tuple does not occur as a foreign key value in a referencing relation. In such circumstances any deletion will leave the database in an invalid state.

There are four strategies that can be used to maintain referential integrity:

• Restricted effect - Any attempt at deletion is disallowed.
• Cascade delete effect - The deletion is allowed and all referenced tuples will also be deleted automatically.
• Nullified effect - The deletion is allowed and all referenced tuples have their foreign key entries set to null.
• Default effect - The deletion is allowed and all referenced tuples have their foreign key entries set to a default value.

Attribute constraints

Allowing null

Null indicates the absence of a value drawn from the domain on which the attribute is drawn. Not all attributes may be allowed to be null.

• Primary keys are not allowed to be null. To allow null would breach the entity integrity rule.
• Alternate keys may or may not be allowed to be null, dependent on the semantics - a GP may or may not have a secretary and is therefore allowed null - a member of staff will always have a NI number and is therefore not allowed null. Good practice is the declaration of these factors.
• Foreign keys are treated in a similar fashion to alternate keys. However an explicit assertion is required to declare the semantics.

Attribute conditions

Some attributes may have constraint definitions on what their values are allowed to be - Registration may be linked to birth year in ascertaining an age of 18 for example.

General constraints

Constraint definitions in relational algebra can be used to represent the participation condition on the 1: side of a 1:n relationship. Constraint definitions using relational algebra can also be used for the inclusivity/exclusivity conditions for a relationship.

Participation conditions

A relation on the 1: side is a referenced relation, whereas the relation on :n side is the referencing relation. If the relation on the 1: side is mandatory, each tuple in the referenced relation has the constraint that the referencing tuple has a foreign key value that is the same as the primary key value of the referenced tuple. In other words, the following expression should produce an empty relation (one which contains no tuples), if the above conditions were applied:

(project Staff over StaffNo) difference (project Student over CounsellorNo)

In a nutshell, if this expression evaluated to an empty relation, each member of staff would be a counsellor for at least one student. This is an example of a mandatory participation condition.

The following constraint definition produces the above constraint. Note the use of is empty. This syntax sets the rule that tuples cannot break this constraint.

relation Staff
StaffNo: StaffNumbers
Name: PersonNames
Region: Regions
primary key StaffNo
constraint (project Staff over StaffNo) difference (project Student over CounsellorNo) is empty

For an optional participation condition the following expression (the same as above) can evaluate to either an empty relation or a tuple(s) containing StaffNo values for those who do not counsel. There is no constraint of is empty, and no constraint on the database state. Although we cannot explicitly represent such an optional condition, it is implicitly represented by the fact that we have not declared a mandatory participation condition.

(project Staff over StaffNo) difference (project Student over CounsellorNo)

Inclusivity/exclusivity conditions

There are four conditions that have to be catered for:

• :n entity type is exclusively involved
• :n entity type is inclusively involved
• 1: entity type is inclusively involved
• 1: entity type is exclusively involved

Attribute constraints handle the :n entity types for both inclusive and exclusive.

:n and exclusive

A car cannot be owned by both the hospital and a member of staff. Therefore, the participation conditions of StaffOwns and HospitalOwns is optional.

Staff (StaffNo, Name)
Hospital (HospitalCode, HospitalName)
Car (RegistrationNumber, TaxationClass, StaffNoOfOwner, HospitalCodeOfOwner)

Where Car.StaffNoOfOwner and Car.HospitalCodeOfOwner are foreign keys of Staff and Hospital respectively.

Car ownership is exclusive to either a member of staff or a hospital. The exclusivity of StaffOwns and HospitalOwns is represented by the fact that at least one of the possible owners must be null. The following constraint definition allows for this.

constraint (StaffNoOfOwner is null or HospitalCodeOfOwner is null)

In the above expression it is allowable for both to be null.

:n and inclusive

If the relationship HospitalOwns was changed to HospitalRegisters, the relationships StaffOwns and HospitalRegisters would be inclusive but optional for Car. If both are mandatory this would force inclusivity. Inclusivity cannot be achieved if one is mandatory and the other optional.

constraint (StaffNoOfOwner is null and HospitalPermitNo is null) or

(StaffNoOfOwner is not null and HospitalPermitNo is not null)

StaffNoOfOwner and HopitalPermitNo must either both be null or both be not null.

1: and inclusive

Entrepreneur(EntNo, Name)
Property (PropertyCode, Value, EntNo)
Shares (ShareCode, Value, EntNo)

Where Property.EntNo and Shares.EntNo are foreign keys referencing Entrepreneur.

Inclusivity means that any Property.EntNo value must also appear as a Shares.EntNo value. If property is owned then shares must also be owned.

Gives entrepreneurs that own property but not shares:

(project Property over EntNo) difference (project Shares over EntNo)

Gives entrepreneurs that own shares but not property:

(project Shares over EntNo) difference (project Property over EntNo)

These could be presented as constraint definitions using the syntax is empty forming part of the declaration of either Shares or Property (not both).

1: and exclusive

If the owning of OwnsShares was exclusive of OwnsProperty then any Property.EntNo value must not appear as a Shares.EntNo value.

constraint (project Property over EntNo) intersection (project Shares over EntNo) is empty

Comments, suggestions, ideas to
Stuart Banner