What you read in this article:
I’m Arsalan Mirbozorgi;
Database propagation constraints specify the behavior of a related table when rows in a target table are updated (Paul Beynon-Davies, 2004, p.108). The primary key to foreign key relationships is used to connect the tables in an Oracle database. Referential integrity can be broken if a user updates a table in a relationship in such a way that the relationship no longer makes sense. For instance, if a department number for ‘Housewares,’ which is a foreign key to another table of departments, is deleted from the department table, the records of employees for Housewares will now refer to a department number that doesn’t exist.
Database management systems (RDBMS) use propagation constraints to maintain correct relationships between tables. According to Beynon-database Davies’s textbook, RDBMS deals with target and related tuples deletions in three ways:
The target row cannot be deleted until all other rows that point to it have been deleted (via foreign keys). To remove Housewares from the departmental table, all of its employees must be deleted, or their departments must be switched.
This delete method, known as cascading delete, deletes the target row as well as all other rows that point to it (by foreign keys). However, the RDBMS would first remove the Houseware employees before removing the department, just as with a restricted delete.
When a row is deleted, all foreign keys (pointing to it) are set to null, which effectively nullifies the delete operation. This means that after the housewares department is eliminated, all of the employees who worked there will have their departmental data set to NULL (unknown).