Integrity endangering operations

There are three types of operation that could potentially endanger referential integrity:

• Inserting of a new tuple
• Deleting an existing tuple
• Updating attribute values of existing tuples

Of course with all these operations there has to be a check on all integrity rules. Selecting values (browsing in the database) does not change any values and is therefore no integrity endangering operation.

Inserting of a new tuple

With this operation all three integrity rules are concerned. The following problems can occur:

• Key integrity: The for this insertion used primary key value is already used in another tuple.
• Entity integrity: The primary key of the new tuple is NULL.
• Referential integrity: A foreign key without an associated primary key is used.

These operations must either be rejected by the database system or transformed into consistent operations according to defined rules.

Deleting an existing tuple

With this operation only referential integrity is concerned. If a foreign key points on a primary key that has been deleted, then the foreign key becomes invalid. The database system should react on a delete operation with one of the following solutions: Abort the operation, cascading delete (also the referenced tuple is deleted!) or set the value of the foreign key to NULL.

By deleting the tupel Div_No "A1" in the relation Division, the division number of the tupel ID "1" in the relation Employee would become invalid.

Updating attribute values of existing tuples

With this operation all three integrity rules are concerned. An update operation can be seen as first a delete of an existing tuple followed by a insert of a new tuple with updated values. Therefore all the above rules must be applied. Problems arise only with the updating of primary or foreign keys. All other attributes can be updated without any restriction.

By modifying the value Div_No in the relation Division from "A1" to "A4", the division number of ID "1" in the relation Employee would become invalid.