EXPLICITt or IMPLIED establishment of referential integrity

  • Thread starter Thread starter John S. Ford, MD
  • Start date Start date
J

John S. Ford, MD

Here's something that's been bothering me. When I link tables or queries
together through the use of subforms (via master and child fields) does that
enforce referential integrity between those tables or queries?

If I set up a such a database, when I open the Tools --> Relationships
window, none of those relationships show up unless I SPECIFICALLY enter them
in the window (by calling up the queries in question and creating the links.

My question is what is the difference between linking them via subforms,
etc. vs. explicitly entering the relationships manually through the
Relationships window? Will the database work any better?

John
 
A carefully designed main form/subform may prevent the user from breaking
referential integrity when entering data via the form, but it will do
nothing to prevent the user from breaking referential integrity if they
access the data directly or via a different application. When you establish
a relationship between tables and enforce referential integrity on that
relationship, referential integrity is enforced by the database engine,
regardless of how the data is accessed.

--
Brendan Reynolds (MVP)
http://brenreyn.blogspot.com

The spammers and script-kiddies have succeeded in making it impossible for
me to use a real e-mail address in public newsgroups. E-mail replies to
this post will be deleted without being read. Any e-mail claiming to be
from brenreyn at indigo dot ie that is not digitally signed by me with a
GlobalSign digital certificate is a forgery and should be deleted without
being read. Follow-up questions should in general be posted to the
newsgroup, but if you have a good reason to send me e-mail, you'll find
a useable e-mail address at the URL above.
 
The only way to enforce RI is to create relationships through the
Relationships window (and to specify there that you want RI: it's not the
default)

Linking through forms and subforms will let you join the data, but won't
give you referential integrity.

As to whether the database will work better, it depends. Creating a
relationship creates indexes on the tables involved, so it should be be more
efficient than not having the indexes, but you should get the same
performance if you manually create the indexes.
 
For referential intergrity to be enforced you must used Tools->relationships
and define the relationships, then for each relationship edit it and check
the box for Enforce Referential Integrity. Once this is done the database
engine (Jet) will require there to be a record on the One side of the
relationship before you can create a record on the Many side. Referential
intergrity will be enforced regardless of how you attempt to enter/edit or
delete data (via forms/subforms, datasheets on tables or queries(eek) or via
individual forms).

Form/subform linking is superficial and is at the User Interface level. This
is just a niceity in which Access allows you to create two forms which are
filtered to be synchronized with each other.
 
Linked Subforms does not enforce R.I..

To enforce R.I. the 2 Tables must be in the same database. On Form /
Subform, you can use Field(s) (or a Control) in the main Form as the
LinkMasterFields and Fields in the Subform as LinkChildFields and Records
for the Subform may come from a Table residing in a different database.
Thus, there is no way that the Form / Subform combination can enforce R.I.
for you.

You need to use the Relationship windows (or DDL SQL) to enforce the R.I.
 
In a word, as long as all data entry and viewing is done through the
forms...yes. Having said that, it's much better to declare relationships and
referential integrity in the Access relationships diagram view. Why? Because
users won't be able to enter non compliant data through some non-form
mechanism as the underlying RDBMS will enforce your data structure "rules'
and you won't have to worry about implicitly defining it though form design
or VBA . Another reason is that your database is more easily upscaled to a
true server platform if the data relationships are defined at the database
layer and not implied in the GUI layer. A final reason is that.....it's the
classy way of doing it and it will impress your friends and neighbors!


Jon
 
Thanks everyone. That helps a lot and clarifies something that's been
bothering me for awhile!

John
 
Back
Top