Easy to Create, Easy to Change - Easy to use!


Relationships and Referential Integrity


Started by Patrice
Search
You will need to Sign In to be able to add or comment on the forum!

Relationships and Referential Integrity

Hi guys, I've got a couple a questions concerning the relationships. I want to try the various options for the referential integrity (cascade, set null, don't update, etc.) and cannot figure out a couple of things.

1. When defining a relationship, why can we specify 2 values for 'referential integrity' ? What's the meaning of each value ?

2. Within your club paradise sample application, I changed the relationship between the MEMBER and FAMILY_MEMBER tables. I set the referential integrity to Null (blank). Then I entered a new record in the MEMBER table and a new FAMILY MEMBER record. I finally deleted my record in the MEMEBER form. As a result, the record in the FAMILY MEMBER table was also deleted... Why doesn't it keep my record in the FAMILY MEMBER table with a blank value for its MEMBER ID field ?

Thank you for you help, Best regards, Patrice

Written by Patrice 21/08/13 at 09:13:21 Dataease [{8}]FIVE

Re:Relationships and Referential Integrity

1.The reason there is two values is because a relationship goes two ways. So the first field is when the first Table is master and the second one child and the second field is when it is used in reverse.

2.The relationship will never define that a related record will be deleted. This only happens when the related record is in a sub form and is not related to the referential integrity.

The referential integrity is only enforced when the relation is used to define a sub-form. It will not enforce key change if the relationship is just defined between two tables.

So for instance if you have built a relationship between a main form (customer) and a subform (contacts) based on customer name, and then change the customer name the following will happen:

Cascade: The customer name is changed in all the sub forms too so that the relationship is still valid.

Null: The key in the subform is blanked and the relationship is lost (and customer name is now blank in Subform).
Don’t Update: No change to sub form, so relationship is broken. All data are still there, but there is no longer link between form and sub form.

Written by DataEase Tech Sup 21/08/13 at 09:14:20 Dataease [{8}]FIVE