We help IT Professionals succeed at work.

Benefits of Referential integrity

wsjwsj2
wsjwsj2 asked
on
Hi Experts,

I am learning to use Access 2007 and have a question on Referential integrity.  When I am joining two tables together, access asks for join type. Such as 1:1, then I have the option to use Referential integrity, but what benefits would it bring me? I googled and get more confused, the answer I got is " it won't delete/update the parent information when the child information referred to is modified."
Comment
Watch Question

Top Expert 2016
Commented:

Referential integrity is a system of rules that Microsoft Access uses to ensure that relationships between records in related tables are valid, and that you do not accidentally delete or change related data.


The following rules apply when you use referential integrity:


You cannot enter a value in the foreign key field of the related table that does not exist in the primary key of the primary table. However, you can enter a Null value in the foreign key, specifying that the records are unrelated. For example, you cannot have an order that is assigned to a customer that does not exist, but you can have an order that is assigned to no one by entering a Null value in the CustomerID field.
You cannot delete a record from a primary table if matching records exist in a related table. For example, you cannot delete an employee record from the Employees table if there are orders assigned to the employee in the Orders table.
You cannot change a primary key value in the primary table, if that record has related records. For example, you cannot change an employee's ID in the Employees table if there are orders assigned to that employee in the Orders table.


reference :  http://support.microsoft.com/?kbid=304467



DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Application Developer
Top Expert 2007

Commented:
" access asks for join type"

Actually, that button should no even be in the Edit Relationships window, because it really has nothing directly to do with the relationship, and only adds confusion to the process. It's doesn't matter what you select (just leave the default).  

It only has to do with when ... in the query designer, if you happen to bring in those two tables, Access with automatically (and arbitrarily to me) create a 'join' (in the query) of the type specified in this dialog box (Join Type).  I prefer to specify my own join type.  I guess they thought this would be a 'convenience' ... but not to me.

mx
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Application Developer
Top Expert 2007

Commented:

Author

Commented:
thanks.