delete operation
the Delete operation can violate only referential integrity. this occurs if the tuple being deleted is referenced by foreign keys from other tuples in the database. to specify deletion, a condition on the attributes of the relation selects the tuple (or tuples) to be deleted. here are some examples.
several options are available if a deletion operation causes a violation. the first option, called restrict, is to reject the deletion. the second option, called cascade, is to attempt to cascade (or propagate) the deletion by deleting tuples that reference the tuple that is being deleted. a third option, called set null or set default, is to modify the referencing attribute values that cause the violation; each such value is either set to NULL or changed to reference another default valid tuple. notice that if a referencing attribute that causes a violation is part of the primary key, it cannot be set to NULL; otherwise, it would violate entity integrity.
in general, when a referential integrity constraint is specified in the DDL, the DBMS will allow the database designer to specify which of the options applies in case of a violation of the constraint.
[cite:@elmasri_db_2015 chapter 5.3.2 the delete operation]
several options are available if a deletion operation causes a violation. the first option, called restrict, is to reject the deletion. the second option, called cascade, is to attempt to cascade (or propagate) the deletion by deleting tuples that reference the tuple that is being deleted. a third option, called set null or set default, is to modify the referencing attribute values that cause the violation; each such value is either set to NULL or changed to reference another default valid tuple. notice that if a referencing attribute that causes a violation is part of the primary key, it cannot be set to NULL; otherwise, it would violate entity integrity.
in general, when a referential integrity constraint is specified in the DDL, the DBMS will allow the database designer to specify which of the options applies in case of a violation of the constraint.
[cite:@elmasri_db_2015 chapter 5.3.2 the delete operation]