Constraints on Supertypes and Subtypes

When you write constraint statements on a supertype table, you can refer to:

Constraints on supertypes are automatically enforced on all subtype manipulations of that supertype.

When you write constraints statements on a subtype table, you can refer to all of the following without joining with another table:

In the following example you only need a single table name in your statement.

EXAMPLE 1:

Suppose Persons can be Guides or Office Staff (subtype set Occupation), and they can be Male or Female (subtype set Gender).

Maternity leave is not possible for guides, only for office staff. This business rule can be enforced by a restrictive constraint with the following statement:

SELECT ' '

FROM female

WHERE maternity_leave = 'Y'

AND guide = 'Y'

In this example, Female is a subtype table, Maternity_leave is a column of this subtype table, and Guide is a subtype indicator of the constellation.

In the following example, you need to join tables only because the business rule refers to subtype columns in different subtype tables:

EXAMPLE 2:

Office staff can only get maternity leave if a number of conditions are met. These conditions refer to office staff attributes such as the person's hire date.

This business rule can be enforced by a constraint with the following statement:

SELECT ' '

FROM female f, staff s

WHERE f.primary_key = s.primary_key

AND f.maternity_leave = 'Y'

AND s.hire_date = condition

AND other conditions