Referential Integrity for Lookup fields in tables

[Mishanya]

I notice that table fields based on query Lookup list from another table
automatically establish one-to-many relationships between those tables. I
also see, that, although Referential Integrity of those tables is not seen to
be checked in the Edit Relationship Dialog box, it is established as well as
Update Cascading, while Delete Cascading is not.
Is my observation right and I don't need to tamper with Relationships
Editing after defining the Lookup fields?

 

[John W. Vinson]

Is my observation right and I don't need to tamper with Relationships
Editing after defining the Lookup fields?

If Access' defaults are ok for you then no. However, do read
http://www.mvps.org/access/lookupfields.htm
for a critique of what many of us consider a misdesigned feature. Lookup
fields are NEVER required; they make it a bit easier to use table datasheets
for data entry, but as a rule you should never use table datasheets for that
purpose!

 

[Mishanya]

Sorry, John, did not catch U.
"If Access' defaults are ok for you then no."
No tampering or no, I'm not right?
Actually I wanted to know if Lookup field does establish Ref integrity with
Update cascading but without Delete cascading?

 

[John W. Vinson]

Actually I wanted to know if Lookup field does establish Ref integrity with
Update cascading but without Delete cascading?

I suspect (I hope!!) that it does neither, but I never use Lookup fields and
would recommend that they never be used in any circumstances.

Let me give it a try...

Ok. Using the Lookup Wizard it creates a relationship with the
dbRelationsDontEnforce attribute set, and no others. Therefore it doesn't
enforce the relationship, nor does it include either cascade. Pretty useless!!

 

[Mishanya]

"Ok. Using the Lookup Wizard it creates a relationship with the
dbRelationsDontEnforce attribute set, and no others. Therefore it doesn't
enforce the relationship, nor does it include either cascade. "

1)Having Lookup field, U can not enter value wich is not in the list not in
the table itself nor in any form based on it.
2)Updating value in the second, LokupList table, automatically updates
respective visible value in the first table ("visible" means the hidden
foreign key value remains the same, but the value from the "visible" colomn
of the Lookup list updates) and in all forms based on it.

If I understand correctly the meaning of RI, it means both RI and Update
Cascading are inforced, although it is not explicited in the Edit
Relationship Dialog Box?

Sorry to bother You on issue You seem to resent to MS - and in the future I
will follow your advise not to use Lookup Wizard. It's just that I've already
built a lot of staff in my DB, and I'm lazing to change all the Lookups, so I
try to understand exactly what means what.

 

[boblarson]

Just a point for your understanding Mishanya -

99 out of 100 times you do not need Cascade Updates (it doesn't mean to
cascade update the data, it means to cascade update the PRIMARY KEY if the
primary key were to change and, if you are doing things right, it really
shouldn't ever change.

 

[Ken Sheridan]

The enforcement of referential integrity per se does not enforce cascade
updates. It requires that a row must exist in the referenced table before
one can be inserted into the referencing table. By implication this also
means that, in the absence of the enforcement of cascade deletes, a row
cannot be deleted from the referenced table while one or more matching rows
exist in the referencing table.

Cascade updates should be enforced where 'natural' keys are used. You might
for instance use State as the key of a table of US states as the values are
distinct, and the column is thus a candidate key. Consequently the foreign
key in a referencing table would also be State. While its unlikely a state
name might change its theoretically possible, so cascade updates should be
enforced. Using an autonumber 'surrogate' key referenced by a long integer
foreign key, there is no need to enforce cascade updates. Natural keys can
be advantageous in some circumstances, e.g. when using correlated combo boxes
in continuous form view.

The enforcement of cascade deletes requires careful thought. You might
decide to enforce cascade deletes in a relationship between Orders and
Customers so that if a customer is removed from the database all their orders
are automatically deleted. However, you would probably not enforce cascade
deletes in a relationship between Cities and Customers as it would make it
too easy for a city to be inadvertently deleted from the database and all
customers in the city in question to be automatically deleted. In this case
enforcement of referential integrity alone would be more appropriate. As a
surrogate autonumber CityID column is likely to be the key of Cities, city
names being duplicated and not suitable as a key, there would be no
requirement to enforce cascade updates either.

Ken Sheridan
Stafford, England

 

[Mishanya]

Got it!

Just a point for your understanding Mishanya -

99 out of 100 times you do not need Cascade Updates (it doesn't mean to
cascade update the data, it means to cascade update the PRIMARY KEY if the
primary key were to change and, if you are doing things right, it really
shouldn't ever change.

--
Bob Larson
Free Tutorials and Samples at http://www.btabdevelopment.com

__________________________________