An aggregation is an association between two classes that represents a part-whole relationship between the classes. See the section "Aggregation, Composition, and Ownership" in Chapter 7 for details. Shared aggregation is
irrelevant for relational schema design, but composite aggregation has an interesting side effect.
Composition says that the composing object owns the other object and that no other object can link to it. This strong form of aggregation corresponds directly to a foreign key with update and delete actions. Figure 11-2 shows a composition association between Person and Identification. As the previous section demonstrated, the Identification class gets the PersonID primary key of Person as part of its primary key. This gives direct meaning to one name for a multiattribute primary key: a composite key. The composition relationship applies to all the subclasses in a class hierarchy as well, though the composition is implicit. The Passport class, for example, inherits both the primary key of the Person table and the additional key column IdentificationID. The foreign key PersonID references the ExpiringID table, however, not the Person table. It still represents a composition, however.
Figure 11-14: The <referential triggered action> SQL Syntax
The special syntax in the <referential triggered action> clause in the referential integrity constraints lets you control some actions that happen when you update or delete the row with the primary key in the owning table [ANSI 1992; Melton and Simon 1993, pp. 221—227]. Updating or deleting the primary values directly implies a change to the
foreign keys in dependent tables. The ON clause tells the DBMS what exactly to do. Figure 11-14 shows the syntax of this clause.
The CASCADE action updates the value in the dependent table when you update the value in the primary key in the owning table. Alternatively, it deletes the row in the dependent table when you delete the row with the primary key to which it refers. Since your foreign key is part of the primary key of the dependent table as well, the CASCADE may cascade to other tables that have foreign keys to that primary key.
The Passport table serves as an example of this action: CREATE TABLE Passport (
PersonID INTEGER NOT NULL REFERENCES Identification ON DELETE CASCADE ON UPDATE CASCADE,
IdentificationID INTEGER NOT NULL REFERENCES Identification ON DELETE CASCADE ON UPDATE CASCADE,
PassportNumber INTEGER NOT NULL UNIQUE,-candidate key IssuingOffice VARCHAR2(100) NOT NULL,
Constraint Passport_PK
PRIMARY KEY (PersonID, IdentificationID))
If you delete or update a PersonID, the DBMS will also delete or update the corresponding Passport PersonID. SET NULL sets the foreign key values to NULL instead of updating or deleting them when you update or delete the primary key row. The SET DEFAULT action sets the foreign key values to the value in the DEFAULT clause of the column.
NO ACTION is the default action. This "lack of action" choice tells the DBMS to do nothing to the foreign key. If you don't handle it with other SQL statements in the transaction, you get an error, and the whole update or delete rolls back.
Instead of using this syntax, you can also code your integrity maintenance in triggers linked to the UPDATE and DELETE events. See the earlier section on "Signals" for details on designing in and using triggers.
Now that I've said all this, now forget it. The key assumption behind all these actions is that you are changing a primary key value. This is not a good idea under most circumstances, with certain exceptions. Object identity should generally be unchanging; there are more implications to changing key values than just cascading an update or delete. Many figurative lives have been lost in the battle to maintain databases that allowed changing primary keys. This is not a good strategy.
If you are using implicit object identity in your design, you should have no reason at all to change your primary key values. PersonID, for example, should not change through your applications. There is no reason to allow it. You may want to change the name, but not the identifier. Some designers don't even expose the identifier through their user interface; it stays encapsulated in their internal objects. The integer keys you generate, by whatever methods, can stay the same forever—as long as you don't run out of integers. You do need to concern yourself about the long- term number of objects you are going to create if you can't change the keys.
There are a couple of situations where this restriction doesn't apply. First and foremost, when you delete a row in a table that owns rows in other tables, you must cascade the delete. This ownership corresponds to composite aggregation in your UML design. There is a degenerate case of this situation in which you order the association to the child table, resulting in a two-part primary key for that table that includes an integer order number. If you allow changing the order of items in the association, you must be able to change the order number in the primary key. This degenerates when other tables depend on that primary key in their turn.
Second, if you are using explicit identity, you will encounter pressure to allow changing primary key values, as they correspond to information that may change in the real world. Ideally, you pick attributes of real-world items that don't change, but you don't always have that luxury. A good general strategy is to move to implicit identity and integer generated keys when you have a candidate key that might change.