Mendip Data Systems

Database applications for businesses and schools

LogoTransparent

Detailed Relationships Analysis  - Part 3 (of 3)

The first two parts of this article explored how relationships are used with particular reference to referential integrity.

 

The article has also shown how the relationships window can get overcrowded resulting in the need to hide tables:

 

 

Colin Riddington      Mendip Data Systems        29/09/2019

* Required

To provide feedback on this article, please enter your name, e-mail address and company (optional) below, add your comment and press the submit button. Thanks

Click to download this article as a PDF file:                              Relationships Advice

 

Click to download the sample database used in this article        MSysRelationships                 Approx 1.5 MB (zipped)

 

 

 

I would be grateful for any feedback on this article including details of any errors or omissions

Difficulty level :   Advanced

Click any image to view a larger version

MSysRel29 MSysRel30

However, if the tables are hidden, how can we still keep a check on the relationships in use?

The answer is to query the hidden system table MSysRelationships where the information is stored

 

By default, system tables do NOT appear in the navigation pane.

To make the table visible temporarily, tick Show Hidden Objects and Show System Objects in Navigation Options

 

The table contains 8 fields – two of these (ccolumn and icolumn) can be ignored

IMPORTANT

System tables are used by Access to make databases function correctly

 

Some system tables can be viewed & a few can be edited

But that doesn't mean you should do so ....UNLESS YOU ARE ABSOLUTELY SURE WHAT YOU ARE DOING

Altering one table may have 'knock on' effects on other tables

 

Incorrectly editing system tables may corrupt your database or prevent you opening it

 

Anyway, having made that point, I'll continue…

 

Although it is ALWAYS important to take great care when viewing system tables,

However, the MSysRelationships table is READ ONLY so no damage can be done.

 

Even so, it is better to create a query than view the system table directly.

The MSys tables themselves have been excluded from this query and aliases used to clarify the purpose of each field

 

SELECT MSysRelationships.szRelationship AS RelationshipName, MSysRelationships.szObject AS TableName, MSysRelationships.szColumn AS FieldName, MSysRelationships.szReferencedObject AS ParentTableName, MSysRelationships.szReferencedColumn AS ParentFieldName, MSysRelationships.grbit AS RelValue

FROM MSysRelationships

WHERE (((MSysRelationships.szObject) Not Like 'MSys*'));

MSysRel31

The query shows the relationship name together with the table and field names being joined.

However, the important field here is a long integer field ‘grbit’ which has been given the alias RelValue

The values indicate the type of join used and whether referential integrity is being enforced

 

The ‘base value’ = 0 is for an inner join with referential integrity using PK field in one table and a foreign key in the other table (1 to many)

The grbit (RelValues) are cumulative. Some examples include:

Here are some examples. The RelValue column should be understandable with reference to the above data

a)   Two Inner Joins - no RI

MSysRel35 MSysRel36 MSysRel37 MSysRel38 MSysRel39 MSysRel40 MSysRel41

e)   Inner 1:1 join on PK fields – with RI and Cascade Update / Cascade Delete

f)   3 left joins (one 1-1; two 1-many) ; all with RI and cascade update/delete

h)   2 inner joins on linked tables – RI cannot be enforced in the front end db

d)   Inner 1:1 join on PK field – no RI

c)   2 inner joins – one with 1-many RI but not cascade update/cascade delete ; other Indeterminate

b)   2 inner joins with RI (1-many) but no cascade update / cascade delete

If you are interested, you can use the supplied query qryRelationships in your own databases to explore other relationship types not covered above

 

I hope the above article has added to your understanding of relationships including the purpose of referential integrity as well as explaining how Access stores this information in the MSysRelationships system table

Click any image to view a larger version

1 2 Return To Top Page 3 of 3 Return to Access Articles 3 MSysRel32 MSysRel34

NOTE:

A SELF join linking PK field to another field will be 1 to many

Hence an inner self join = 1 (using RI) or 3 (without RI)

g)   Outer self join without RI

MSysRel42