Consider Enforcing Referential Integrity in Ms Access

When we look at thereferentialIntegrityDeploymentArchitecture advantages of using relational databases like Access over spreadsheets, we find that their capacity to enforce referential integrity makes a significant difference. For those unfamiliar with the concept of referential integrity, the term essentially refers to shared relationship between multiple tables in an RDBMS. The concept can be best explained with an example. Consider an employee database used for disbursal of salary and other benefits. It has several tables with one named Employee Master while others like Employee Salary and Leave Record too being present. Each employee in the Employee Master table has an unique code, called primary key, which makes it possible for the table to be indexed and data to be sorted. Now every record in Employee Salary would include the employee ID as a Foreign key for the specific employee which would directly point to the primary key present in Employee Master table. If referential integrity is enforced, any deletion of an employee record from the Employee Master table will remove the associated records from the related tables. This essentially is the cascading delete property of referential integrity. Referential integrity helps you maintain data consistency and make changes to related records in one go.

 

Enforcing Referential Integrity in Ms Access

While enterprise class RDMBS solutions like SQL Server and Oracle enforce referential integrity by default, in case of Ms Access you have to explicitly ensure it. This is achieved when you are setting relationships between tables and herein you need to hit the Enforce Referential Integrity option.  Once enforced several referential integrity rules like cascading delete or updates would come into the picture. Hence if you are enforcing the rule, you should ensure that you do not inadvertently delete related data. For example in the above example if you happen to change the employee ID associated with an employee at any point of time, the change would be reflected on all the linked tables which references the primary key. Moreover widespread changes can sometime skip your notice and thus it necessary to always consider the coverage of changes and make them after proper study.

 

An Access Crash can prove to be a disaster for your business

If you are using the Ms Access application for managing your business data then you should be aware of the risk associated with an Access database. While the application may be rich in features, it tends to store all data in a single MDB file which is vulnerable to corruption. So if you are looking to secure your data from the vagaries associated with an Ms Access crash, investing in an access repair tool like DataNumen Access Repair would be the right thing to do. It has the highest recovery rates in its class and is extremely easy to use. Moreover the application can work on most media types and is also equipped to handle the recovery of forms and indexes.

 

Author Introduction:

Alan Chen is President & Chairman of DataNumen, Inc., which is the world leader in data recovery technologies, including access repair and sql recovery software products. For more information visit www.datanumen.com

Comments are closed.