Referential integrity constraint

[dsaxena15]

Hey guys,

I'm new at MS-Access. I don't clearly understand what "referential integrity constraint" means. I looked up online but all I get is fancy definitions which don't make sense.

Also, if someone could explain to me what is "cascade update related fields" and "cascade delete related records".


I get these three options when I try to create a relationship between 2 tables. Thanks a lot!

[John_G]

Hi - I'll give it shot.

"Referential integrity" means that the data in the tables must conform to the relationships which have been defined. This is best illustrated by an example. Let's take the common one of customers and orders. You would have a customers table, and an orders table, and let's assume a relationship has been defined between the two using the Customer_ID. The relationship between Customers and Orders is one-to-many, i.e. one customer can have many orders. The customers each have a unique ID, and when entering a new order, you would indicate which customer the order was for by entering the Customer ID on the order form.

The concept of referential integrity means that (in this case) for an order to be valid, it a) must have a Customer ID entered, and b) the Customer ID must exist in the Customer table; in other words, a record in the Orders table cannot exist without a corresponding record in the Customers table. (However, this does not prevent there being Customers who do not have any orders yet.) Maintaining Referential integrity also means that a Customer record cannot be deleted if there are any corresponding records for that customer in the Orders table, because we just said that Orders must have corresponding Customers. But this all leads to your next question...

Cascade update related fields means that for example if you change a Customer's ID number, the ID number change will also be made on all orders for that customer. If when you define the relationship, you do not allow cascade updates, then you would not be able to change a Customer_ID, because that would result in orders with no corresponding customer.

Cascade delete related records means that deleting a record on the "one" side of a one-to-many relationship will also delete all related records on the "many" side. In other words, when Cascade Delete is allowed, deleting a customer will also delete all orders for that customer. You have to be absolutely sure of what you are doing before enabling cascade delete, especially when data on the "many" side is of historical or financial significance.

My own opinion is that whenever possible you should enforce referential integrity in your relationships - it prevents all sorts of errors from being made. Use the cascade update/delete only when you know what the effects will be.

HTH

John

[tanvi]

Referential integrity is a system of rules that Microsoft Access uses to ensure that relationship data is valid and that you cannot accidentally delete a record in one table if a matching record is present in a related table. For detail information please visit this link: http://www.ehow.com/how_13625_unders...integrity.html

Cascading updates or cascading deletes are used to overrule referential integrity rules while retaining relationship integrity. These conditions only apply to relationships in which referential integrity is enforced.

[dsaxena15]

You guys are awesome! Thanks a lot! I really appreciate the help! Thanks again!