Database Reference
In-Depth Information
15.3 Foreign Key Constraint Specification
Some implementations of SQL (example early versions of Gupta SQL-Windows) do not
provide the designer the flexibility of specifying which attribute(s) of a referenced table
are to be considered when a foreign key constraint is defined.
Example 2: Referring to the college database of Chapter 7, the following is what a
Gupta SQL-Windows specification for the foreign keys of the Pgm_Struct table would
look like:
Note: Gupta SQL-Windows has since been upgraded to Team Developer; therefore,
this example is not a commentary on the current product. The [previous SQL] syntax did
not allow for the designer to specify which attribute in the referenced relation is to be
used. Rather, the DBMS chose the attribute with similar characteristics to the referencing
attribute (i.e. the foreign key). This idea is good for basic scenarios; however, if there
are more than one attribute in the referenced relation with similar characteristics to the
foreign key, confusion could arise.
Work-around: The obvious work-around for a scenario like this is to allow the
designer to explicitly specify which attribute of the referenced relation is to be used in the
foreign key constraint. Oracle does this quite nicely; the specification for this scenario
might be as follows:
15.4 Superfluous Enforcement of
Referential Integrity
Some implementations of SQL exhibit a superfluous enforcement of the referential
integrity rule with respect to update of non-key attributes of tuples in a referenced relation.
Example 3: Suppose that we have a course — M100 College Algebra — that occurs in
all academic programs. We wish to change the description of the course to “Elementary
Algebra”, thus:
 
Search WWH ::




Custom Search