Delete Propagation - Teradata Meta Data Services

Teradata Meta Data Services Programmer Guide

Teradata Meta Data Services
Release Number
English (United States)
Last Update
Product Category
Teradata Tools and Utilities

Delete Propagation

Delete propagation means recursively deleting any child (destination) object that was “contained by” a parent (origin) object that itself is being deleted if no other object contains the child. This functionality is useful in containment situations where deletion of a parent object implies that certain of its contained child objects no longer exist.

For example, if the MDS repository holds an object t representing a database table, an object r representing one of its rows, and a contains relationship relating these as “table t contains row r,” if t is deleted, r should also be deleted automatically through delete propagation if t was the only object that contained r.

Delete propagation is turned on and off at the relationship description level. Each relationship description object has a PropagateDeleteFlag which, if set on, will cause deletions involving that relationship to be propagated.

The delete propagation MDS provides implements a semantics of containment. Enabling delete propagation for a given relationship is equivalent to marking it as a containment relationship. Doing this reduces the status of the destination objects of this relationship type: it indicates that they are subordinate objects that depend for their existence on being contained by at least one superordinate object through a relationship that has delete propagation enabled.

A containment relationship means a relationship type that has its delete propagation flag turned on, and if object A is said to contain object B, it means that A is the origin object and B is the destination object of a containment relationship that links them as shown in Figure 16.

In the following example, Object A is said to contain Object B, because A points to B with a relationship whose PropagateDeleteFlag is set.

Figure 16: Delete Propagation Example 1

An object will be deleted by delete propagation if it was previously contained and the last object that contained it gets deleted. That is, if an origin object of a given destination object is deleted and it points to the destination object with one or more relationship types for which delete propagation is enabled, the destination object will also be deleted if there are no other origin objects pointing to it with any relationship types for which delete propagation is enabled.

Deleting an object is affected by delete propagation as follows: when an object in the MDS repository is deleted, all relationship instances of which it is the origin or destination will also be deleted (regardless of whether their delete propagation flags are set). For each such relationship in which the deleted object was the origin of a containment relationship, MDS will check the destination object. If the destination object is not contained by any other object (that is, the destination object is not pointed to by any other relationships that had delete propagation enabled), MDS will recursively delete that destination object.

Because delete propagation is a recursive process, deletions may propagate through many layers of relationships and objects when the user deletes a single object. This has two important implications:

  • Delete propagation can be dangerous. Large portions of the network of objects in the repository can potentially be deleted when the user explicitly deletes a single object.
  • Delete propagation can be expensive. Many layers of recursion involving numerous database lookups may be required to completely propagate a delete.
  • For these reasons, and especially the first one, delete propagation should only be turned on for a containment relationship type for which the user is absolutely certain that deleting an origin object always means its contained objects should be deleted if they are not contained by any other origin objects. The fact that a contained object participates in other non-containment relationships will not prevent it from being deleted by delete propagation if its last containing parent is deleted.

    Delete propagation functionality is provided expressly for containment situations where deletion of a parent may imply deletion of a child. This is why it will delete an object even if the object is involved in other non-containment relationships.

    For example, if tables contain rows and a table is deleted, a contained row should also be deleted even if it has another relationship such as “row r was_inserted_on date d,” where r is the row object and d is an object specifying the row’s insertion date. In the example below, the left side is before deleting table t, and the right is after deleting table t. Because table_contains_row has its PropagateDeleteFlag on, it is a containment relationship, so delete propagation also deletes row r. Because was_inserted_on has its PropagateDeleteFlag off, it is not a containment relationship, so deletion will not propagate to the date d object.

    Figure 17: Delete Propagation Example 2

    A single destination object can be contained by multiple origin objects. The destination object will only be deleted by delete propagation when its last containing parent is deleted.

    For example, assume the repository held a database table object t, a spreadsheet object s, a row object r, and two containment relationships (relationships with delete propagation enabled): table_contains_row and spreadsheet_has_row.

    If these objects are related by “t table_contains_row r” and “s spreadsheet_contains_row r,” the same row r is contained by both the table and the spreadsheet.

    Both table t and spreadsheet s contain row r, so delete propagation will not delete r unless both s and t are deleted.

    As long as either the table or the spreadsheet still exists, the row can continue to exist. If the user deletes the table, the row will not get deleted by delete propagation because it is still the destination of another containment relationship, namely “s spreadsheet_contains_row r.”

    Figure 18: Delete Propagation Example 3