How to Create Relationships with Referential Integrity in Microsoft Access

Description

Relationships

A database relationship defines how two tables connect to each other. The Relationships Diagram is a good way to get an overall view of what a database is keeping track of. As you build tables in Microsoft Access, or link to tables that are in SQL Server, Oracle or other big data -- or Excel range, text file, or wherever your data is (after all, it is Access!), it is a good idea to put all the tables on the Relationships Diagram, even if they don't relate to anything. 

Stretch* or shrink** field lists so everything shows -- and rearrange tables so the "1" side of the relationship is on the left (where values originate), and the "many" side on the right ... so data flows from left-to-right as data must be created.

* stretch field lists wider and taller to show full names, and all the names. Drag another copy of the table to the diagram if it has numerous fields -- and adjust scrollbars to see everything. 

** shrink: do you have extra space to the right or below in field lists?  long names? Maybe put the descriptive stuff in the Description and keep names short? Be sure to show the full table name too. No need to waste real estate on the screen with unnecessary space, so close the gaps (on forms and reports too! gaps are wasted space unless you want them, of course) and tighten things up.

You can put queries on the diagram too ... perhaps just temporarily, to document them ~ 

Document the Diagram

Once you see everything on the Relationships Diagram, take a screen shot (or or 2 or 3, ...). If you don't have a clipping program like Snagit, you can press the Print Screen key (maybe Fn + PrintScreen) to copy an image of the screen to the Windows Clipboard. Then paste to Word, PowerPoint, or other program, add explanations and annotations if desired, and Save.

Relationships Diagram

Create Relationship

To create a relationship in Access, first make sure the data type (and size) of the fields you want to join are the same.

Data Type and Size

It is common for numeric key fields to be Long Integer. AutoNumber is a special form of Long Integer that automatically gets numeric values as records are created.

In this example, you see that PhoneTypeID is an AutoNumber in the PhoneTypes table, where it is actually a Long Integer, and is also the Primary Key. In the related table, PhoneTypeID is a Long Integer Foreign Key with no Default Value.

Relationship from AutoNumber PK to Long Integer FK

If you are joining on text, make sure the field size matches and is not ridiculously big. Do not match on floating point numbers such as Single or Double; they are not accurate for exact comparisons.

Steps

On the Relationships Diagram:
1. click on the Primary Key in the main table (or a field that has a Unique index), 
2. drag to the Foreign Key in the related table,
3. and let go.

4. In the dialog box that pops up,
check: Enforce Referential Integrity

Error creating relationship?

If there is bad data, a relationship with referential integrity cannot be created. Fix the bad data and try again.  If you do not know how to fix it, try the Query Wizard (Create ribbon) and choose "Find Unmatched". The video illustrates deleting the record with bad data. That was because, in this case, a phone number with no contact has no relevance.  Alternately, you can keep the data and delete the bad foreign key value so referential integrity can be enforced on the relationship.

Linked Tables

If a table is linked, you can show an additional relationship on the diagram, but you cannot create a real relationship (one with RI) that is not already there. If relationships are not defined, you can drag lines without enforcing referential integrity to document what is supposed to match.

Summary

Define relationships and arrange the diagram as you build your tables. It is much easier to do this as you go than it is to back-track ... and creating the relationships will often surface other issues that need to be addressed. Also think about Indexes. This video doesn't get into those, but they deserve attention too.

Enforce Referential Integrity, unless you have a specific reason not to. 

Please Like, Comment, and Share ~ thank you 

have an awesome day,
crystal (strive4peace) 
~ connect to me, let's build it together

 

Embed

Download

Download this episode

Download captions