References:

1 X3H2-93-359R/ISO DBL MUN-003, (ISO-ANSI Working Draft) Database Language SQL (SQL3), Jim Melton (ed.), August 1993.

1 DISCUSSION

2 ACTION PROPOSED

Semantics of the INSTANCE Keyword

The INSTANCE keyword introduced with the adoption of [2] has some subtle semantic implications which may not have been intended. These are illustrated using the example

CREATE TABLE EMP
(PERSON_DATA PERSON,
MANAGER PERSON,
SPOUSE PERSON INSTANCE,
...)

The semantics:

• The spouse of every employee will be represented by an OID, even if the employee has no spouse. They all count as instances of PERSON. This very subtle distinction between no spouse and a null-valued spouse may not be fully understood.
• The OID in a spouse slot can never change. Remarriage cannot be recorded. Attributes of an employee's spouse may change, including name, date of birth, social security number, parents, etc., etc., - but the identity of the spouse will not change.
• Uniqueness is forced within the column. Polygamy cannot be recorded. No two employees can have spouses with the same OID.
• If several attributes are declared as PERSON INSTANCE, then uniqueness is cumulatively forced across all such columns, even in different tables. If MANAGER had been so declared, then the same person cannot be both a manager and a spouse.
• Assignment behaves anomalously when the target is an INSTANCE attribute. If Dick's spouse is assigned to be Jane's manager, then Dick's spouse will be equal to Jane's manager. However, if Jane's manager is assigned to be Dick's spouse, they will not be equal.
• INSTANCE may only be specified in base tables. If STUDENT and TEACHER are defined as subtypes of PERSON, table designers do not have the option to store student attributes separately from teacher attributes.

[Editorial note. Was that last point made clear in the adopted changes? If not, then that also needs clarification in the standard.]