Creating Relationships II
The first option is "Enforce Referential Integrity". We have already studied this concept. If you want to select this option, the related Field in the Father Table needs to be the Primary Key or it needs to have a unique Index. Also, the fields need to have the same Data Type.
After we select this slot, the other 2 options (slots) will become available. We can also select them if we deem necessary.
With the option "Cascade Update Related Fields", every time we change a Record in the primary key of the "Father table", Access will automatically update the value in the corresponding field for those related fields.
With the option"Cascade Delete Related Fields", every time we eliminate a record from the "Father table", Access will automatically delete those Related Records in the secondary Table (Son Table).
BE CAREFUL. By default, Referential Integrity doesn't allow us to eliminate (delete) any Record form the "Father Table" if it is related to other Records in the "Son Tables".
If we click on the option "Cascade Delete Related Fields", Access, to comply with the principle of Referential Integrity, will delete ALL the records from the Son Table which depend on this data in the Father Table. Therefore, even if Access asks for your confirmation to perform this action, I advise you to be prudent when using it. It won't be the first time someone erases every record in the son table by mistake just by eliminating a simple piece of Data in the Father Table.
Once we click on "Create", we will see this image:
These are the Tables we have related: "Clients" and "Orders". Their common field is the "Client's Code" from the "Clients" Table, related to the "Client's Code" field of the "Orders" table (see the line between them). We can see that next to the "Client's Code"from the "Clients" Table, there is a "1" (One) and next to the "Client's Code" of the "Orders" Table, there is an "Infinity" symbol, which Access uses to represent "Many". This two symbols represent the type of relationship. (One to Many)
In case you want to keep this relationship, you need to save it.
