Propagation constraint: Difference between revisions

Revision as of 15:25, 29 January 2007

Latest revision as of 04:55, 27 April 2023

(11 intermediate revisions by 11 users not shown)

Line 1:

AIn database systems, 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 to 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 constraints are methods used by 'Relational[[relational Databasedatabase Managementmanagement Systems'system]]s ([[RDBMS]]) to solve this problem by ensuring that relationships between tables are preserved without error. In his database textbook, Beynon-DaivesDavies explains the three ways that RDBMS handle deletions of target and related [[tuple]]s:

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.

* '''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, or their departments changed, before removing the department from the departmental table.

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:

* '''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.

* Restricted'''Nullifies Delete''' - the user cannotcan delete the target row untiland all rowsforeign thatkeys point(pointing to it) (viaare set to [[foreignNull key(SQL)|null]]s). haveIn beenthis deleted.case, Thisafter meansremoving thatthe allhousewares Housewaresdepartment, employees wouldwho needworked toin bethis deleteddepartment bywould thehave usera before[[Null removing(SQL)|NULL]] the(unknown) departmentvalue fromfor thetheir departmental tabledepartment.

* 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==

*Beynon-Davies, P. (2004) ''Database Systems'' Third Edition, [[Palgrave Macmillan]].

[[Category:Relational database management systems]]

{{Compu-stub}}

[[Category:Data modeling]]