Propagation constraint: Difference between revisions

Revision as of 17:18, 8 August 2006

Latest revision as of 04:55, 27 April 2023

(15 intermediate revisions by 15 users not shown)

Line 1:

In 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.

{{context}}

AccordingPropagation toconstraints [[Beynon-Davies]]are (2004,methods p.108)used by "''[a][relational propagationdatabase constraintmanagement detailssystem]]s what should happen(RDBMS) to asolve relatedthis tableproblem whenby weensuring updatethat arelationships rowbetween ortables rowsare ofpreserved a targetwithout table''"error. HeIn furtherhis statesdatabase thattextbook, forBeynon-Davies allexplains [[relationship]]s,the onethree shouldways definethat how toRDBMS handle deletions of target and related [[tuple]]s. There are three possibilities:

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

* Cascades'''Restricted Delete''' - canthe user cannot delete the target row anduntil all rows that point to it (via [[foreign keyskey]]s) arehave alsobeen deleted. This means that all Housewares employees would need to be deleted, or their departments changed, before removing the department from the departmental table.

* Nullifies'''Cascades Delete''' - can delete the target row and all foreignrows keysthat (pointingpoint to it (via foreign keys) are setalso todeleted. [[NullThe (SQL)|null]]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 have a [[Null (SQL)|NULL]] (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]]