Now let's create a query that returns the names of all customers who have ordered a product. If you hadn't enforced referenctial integrity, Access would have happily let you enter the meaningless values and the referential integrity of your database would have flown out the window. Note that you will only get this message if you have enabled Enforce Referential Integrity.

In this case I tried to enter a CustomerId of 50 and a ProductId of 50, but my Customers and Products tables don't have a CustomerId of 50 or a ProductId of 50: If you had enforced referential integrity on the relationship (as we have), Access would prevent this from happening.īelow is a screenshot of what will happen if you attempt to enter a value that would breach referential integrity.

In this case, referential integrity has not been maintained. Using our tables as an example, let's say you have 10 records in the Customers table (with a CustomerId of 1 to 10), but your Orders table contains a record using a CustomerId of 11. This prevents foreign key values from having no corresponding primary key value in the referenced table. When you create a relationship, you also have the option to Enforce Referential Integrity (which we selected when we created our relationship).

Microsoft access 2013 tutorial video