AlphaPress Publishing
P.O. Box 5942 Albany, NY 12205
Tel: 518-250-3890
Email: alphapress@hotmail.com  


  
Click to buy this book from Amazon

Section Menu 

News

Pindaro is now working on a comprehensive book on SQL Server 2014.  For any comments you can email the author at alphapress@hotmail.com.

  

 

 

What is referential integrity, how to apply it, and what it means.

To contact the author click here

This article is from the book "Access 2007 Pure SQL

To download the sample database click here


Referential Integrity is a concept misunderstood and underused in the database professional world.  Some developers will turn this option on in Access because it is a good "thing" to do, and it is, without fully understanding its implications.   Let us take it one step at a time and explain the ins and outs of referential integrity. 

Referential integrity in relational databases means that relationships among joined tables remain consistent.  Of course, as is the case with all definitions, we do not understand much.  Let us try to approach it from a more practical perspective.  First, we set referential integrity on table relationships (not tables themselves) and we have the choice to apply it when we create those relationships.


Let us see how we do it and then we will discuss what it means.  Let us say that we would like to create a one-to-many relationship between customers and orders.  To achieve this we drag the CustomerID field (PK in the Customer table) on the CustomerID field in the Orders table (FK in the orders table).  The relationships window then opens up as we can see below:


The only thing we need to do is to click on the Referential Integrity check box. In some cases, you might find that the database does not allow you to apply referential integrity.  If this is the case, then you should immediately look for orphaned records in the table at the many part of the relationship.  In this particular example, it means that we have orders with CustomerID (FK) values without corresponding CustomerID values (PKs) in the customers table.  In short, we have orders without corresponding customers.  Check chapter 22 for a full explanation of orphaned records.  I have devoted a whole chapter on orphaned and unrelated records because this topic is crucial in any database work.   

Once referential integrity is on, we have several consequences.  First, the database will not allow us to enter an order in the orders table without a corresponding customer.  If we have an order from a new customer, then we have to enter that customer’s data in the Customers table first and then his or her orders in the Orders table.  Actually, this is what you will see if you try to enter an order without an existing customer: 

 

Second, the database will not allow us to delete a customer who does have orders in the database.  First, we need to delete all the orders for that customer and then try to delete the customer.  If we try to delete a customer with existing orders, then the following message will appear. 


If you want to delete a customer with existing orders in one step, then you can also click on “Cascade Deletes”.  This option helps the professional delete all related information about a customer in the database but it is very dangerous to leave on because end users might delete customers and their related information by mistake.  Check chapters 27 and 28 on "Cascade Updates" and "Cascade Deletes" in this book for full details on their use since they are an important productivity tool for developers and power users.