1-866-245-5224 sales@keystonelearning.com

Access 2007: Referential Integrity

Access 2007: Referential Integrity

Referential Integrity is a system of rules that Access can apply when you begin to create relationships. With Referential Integrity, you can be sure that your relationships are valid because when Referential Integrity is enforced:

     
  • You can’t enter a value in the foreign key field of one table if there is not a matching value in the primary key of the related table.
     
  •  
  • You can’t delete a record from the primary table if a matching record exists in the related table.
     
  •  
  • You can’t change the value in the primary key of the primary table if there are related records in the related table.
     

But, before you can set referential integrity, you’ll need to make sure that:

     
  • The matching field from the primary table is a primary key or has a unique index.
     
  •  
  • The related fields have the same data type.
     
  •  
  • Both tables belong to the same Microsoft Access database. If the tables are linked tables, they must be tables in Microsoft Access format, and you must open the database in which they are stored to set referential integrity.
     

I often tell my students that you really only to be sure of three (3) things when designing your table. If you can remember the following three things, you’ll have no problem enforcing referential integrity when you need to.

Related fields should have:

     
  1. Same Name
     
  2.  
  3. Same Data Type
     
  4.  
  5. Same Field Size