Propagation constraint: Difference between revisions

Revision as of 05:51, 12 November 2006

Revision as of 15:25, 29 January 2007

Line 1:

{{context}}

A propagation constraint "details what should happen to a related table when we update a row or rows of a target table''" (Paul Beynon-Davies, 2004, p.108). Tables are linked using [[primary key]] to [[foreign key]] relationships. It is possible for users update one table in a relationship in such a way that the relationship is no longer consistent and this is known as breaking [[referential integrity]]. An example of breaking referential integrity: if a table of employees includes a department number for 'Housewares' which is a foreign key to a table of departments and a user deletes that department from the department table then Housewares employees records would refer to a non-existent department number.

Propagation constraint in a [[relational database]] refers to how consistency is maintained between coupled tables. According to [[Beynon-Davies]] (2004, p.108) "''[a] propagation constraint details what should happen to a related table when we update a row or rows of a target table''". He further states that for all relationships, one should define how to handle deletions of target and related [[tuple]]s. There are three possibilities:

Propagation constraints are methods used by 'Relational Database Management Systems' ([[RDBMS]]) to solve this problem by ensuring that relationships between tables are preserved without error. In his database textbook, Beynon-Daives explains the three ways that RDBMS handle deletions of target and related [[tuple]]s:

* Restricted Delete - cannot delete the target row until all rows that point to it (via [[foreign key]]s) have been deleted.

* Cascades Delete - can delete the target row and all rows that point to it (via foreign keys) are also deleted.

* Restricted Delete - the user cannot delete the target row until all rows that point to it (via [[foreign key]]s) have been deleted. This means that all Housewares employees would need to be deleted by the user before removing the department from the departmental table.

* Nullifies Delete - can delete the target row and all foreign keys (pointing to it) are set to [[Null (SQL)|null]].

* Cascades Delete - can delete the target row and all rows that point to it (via foreign keys) are also deleted. The process is the same as a restricted delete, except that the RDBMS would delete the Houseware employees automatically before removing the department.

* Nullifies Delete - can delete the target row and all foreign keys (pointing to it) are set to [[Null (SQL)|null]]. In this case, after removing the housewares department, employees who worked in this department would not have a [[NULL]] or unknown value for their department.

==Bibliography==