Referential Integrity.


Referential integrity is a system of rules that relational database systems use in order to ensure that data stored in related tables are valid.

In Access 2003, we can activate these rules by checking the Enforce Referential integrity box when relationship is created o modified.

If Referential Integrity is checked, Access will not allow us to insert a record in the related table if there is no matching record in the primary table.

E.g: We have a residents table and a Cities table, in the Residents table I have a City field which indicates in which city the resident lives in, the two tables should be related by the City field. In this kind of relation (one-to-many) the Cities table is the primary table and the Residents table the related table (a city has many residents, an a resident lives in one city). By checking the Enforce Referential Integrity box, we will not be allowed to insert a resident with a city that does not exist in the Cities table.

Referential integrity has two associated actions:

Cascade update the related fields: If checked, when a value is changed in the related field in the primary table, the values in its related records in the related table will be automatically changed.

E.g: If we change the name of a city in Cities table, then automatically in the Residents table all the residents from this city will change to the new value.

Cascade delete related records: If checked, when a record is deleted from the primary table, all of the related records in the related table will also be deleted. E.g: If we delete a city in the Cities table, all the residents from this city are automatically deleted from the Residents table.

If options are not selected, Acess does not allow us to change the name of a city or eliminate a city if it has any residents assigned.

 

   
   

Avi

so legal: Uso off-line autorizado a alumnos de aulaClic exclusivamente. No está permitido utilizar estos cursos en academias o centros privados.

© aulaClic. All rights reserved. Reproduction in any form whatsoever is prohibited. April-2006.